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

On Thu, Jun 12, 2014 at 1:36 PM, Antonio Fernández Pérez
<antoniofernandez@stripped> 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
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