Hi, Would you try STRAIGHT_JOIN?
mysql56> ALTER TABLE masik DROP KEY idx_test, ADD KEY idx_test(szam, id); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql56> EXPLAIN SELECT e.id FROM masik m STRAIGHT_JOIN egyik e ON e.id= m.id WHERE e.duma= 'aaa' ORDER BY m.szam ASC; +----+-------------+-------+--------+---------------+----------+---------+---------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+----------+---------+---------+------+-------------+ | 1 | SIMPLE | m | index | m_idx | idx_test | 8 | NULL | 2 | Using index | | 1 | SIMPLE | e | eq_ref | PRIMARY,e_idx | PRIMARY | 4 | d2.m.id | 1 | Using where | +----+-------------+-------+--------+---------------+----------+---------+---------+------+-------------+ 2 rows in set (0.00 sec) This may be faster than original situation if e.duma doesn't have a well cardinality or you can use LIMIT clause. This can eliminate a temporary table and file, but this needs whole index scan of m.idx_test. Regards, yoku0825 2014-06-12 20:42 GMT+09:00 Lay András <and...@lay.hu>: > Hi! > > On Thu, Jun 12, 2014 at 1:36 PM, Antonio Fernández Pérez > <antoniofernan...@fabergames.com> wrote: > > > Hi Lay, > > > > If I don't mistake, you can't eliminate "Using temporary" and "Using > > filesort" because you are using an "order by". Try the explain again > > removing order by and check the output. > > Thank you, I know, without order no problem: > > (root@localhost) [test]> explain select e.id from egyik e,masik m > where e.id=m.id and e.duma='aaa'; > > +------+-------------+-------+------+---------------+-------+---------+-----------+------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len > | ref | rows | Extra | > > +------+-------------+-------+------+---------------+-------+---------+-----------+------+--------------------------+ > | 1 | SIMPLE | e | ref | PRIMARY,e_idx | e_idx | 767 > | const | 1 | Using where; Using index | > | 1 | SIMPLE | m | ref | m_idx | m_idx | 4 > | test.e.id | 1 | Using index | > > +------+-------------+-------+------+---------------+-------+---------+-----------+------+--------------------------+ > 2 rows in set (0.02 sec) > > > When you use an order by, MySQL needs to use filesort and spends some > time > > sorting the result set. Also, create a temporary table with the rows > > prevously. This is the reason. > > Not every situation. If the order column in the first table, no problem > too: > > (root@localhost) [test]> explain select e.id from egyik e,masik m > where e.id=m.id and e.duma='aaa'order by e.duma asc; > > +------+-------------+-------+------+---------------+-------+---------+-----------+------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len > | ref | rows | Extra | > > +------+-------------+-------+------+---------------+-------+---------+-----------+------+--------------------------+ > | 1 | SIMPLE | e | ref | PRIMARY,e_idx | e_idx | 767 > | const | 1 | Using where; Using index | > | 1 | SIMPLE | m | ref | m_idx | m_idx | 4 > | test.e.id | 1 | Using index | > > +------+-------------+-------+------+---------------+-------+---------+-----------+------+--------------------------+ > 2 rows in set (0.00 sec) > > But i need to order with column in the second table. > > Bye! > > Lay > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
