List:General Discussion« Previous MessageNext Message »
From:Lay András Date:June 13 2014 8:29am
Subject:Re: Order column in the second table
View as plain text  
Hi!

On Fri, Jun 13, 2014 at 3:59 AM, yoku ts. <yoku0825@stripped> 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
Thread
Order column in the second tableLay András12 Jun 2014
  • Re: Order column in the second tableAntonio Fernández Pérez12 Jun 2014
    • Re: Order column in the second tableLay András12 Jun 2014
      • Re: Order column in the second tableyoku ts.13 Jun 2014
        • Re: Order column in the second tableLay András13 Jun 2014