List:General Discussion« Previous MessageNext Message »
From:yoku ts. Date:June 13 2014 1:59am
Subject:Re: Order column in the second table
View as plain text  
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 <andras@stripped>:

> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
>

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