Hi! On Fri, Jun 13, 2014 at 3:59 AM, yoku ts. <yoku0...@gmail.com> wrote:
> 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. Thank you, for your idea, i improved it a little: ALTER TABLE egyik DROP KEY e_idx, ADD KEY e_idx(duma,id); (root@localhost) [test]> EXPLAIN SELECT e.id FROM masik m STRAIGHT_JOIN egyik e use index(e_idx)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 | ref | e_idx | e_idx | 771 | const,test.m.id | 1 | Using where; Using index | +------+-------------+-------+-------+---------------+----------+---------+-----------------+------+--------------------------+ 2 rows in set (0.00 sec) In this case, both querys using indexes! Thanks for the suggestion! Lay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
