# Order by optimisation problems **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 9, 2007, 9:55am UTC](https://forums.percona.com/t/order-by-optimisation-problems/520 "2007-11-09T09:55:15Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![icydee](https://avatars.discourse-cdn.com/v4/letter/i/b38774/32.png) [@icydee](https://forums.percona.com/u/icydee)\ **Post date:** [November 9, 2007, 9:55am UTC](https://forums.percona.com/t/order-by-optimisation-problems/520/1 "2007-11-09T09:55:15Z")
MySql 5.0.22-log I am trying to optimise an ‘order by’ where I have a join between tables. I have reduced the problem to the following minimal one. CREATE TABLE `cust` ( `id` bigint(20) unsigned NOT NULL auto\_increment, `name` varchar(25) NOT NULL default ‘’, `username` varchar(32) NOT NULL default ‘’, PRIMARY KEY (`id`), KEY `name` (`name`), KEY `username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO\_INCREMENT=100577 ;CREATE TABLE `trans` ( `id` bigint(20) unsigned NOT NULL auto\_increment, `cust_id` bigint(20) unsigned NOT NULL default ‘0’, `created_by` varchar(10) NOT NULL default ‘’, `trans_date` datetime NOT NULL default ‘0000-00-00 00:00:00’, PRIMARY KEY (`id`), KEY `cust_id` (`cust_id`), KEY `created_by` (`created_by`), KEY `trans_date` (`trans_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO\_INCREMENT=259045 ;explain SELECT \* FROM cust, trans WHERE cust.id = trans.cust\_id AND ( cust.name LIKE ‘MS%’ OR cust.username LIKE ‘MS%’ ) order by trans.trans\_date limit 0,10;±—±------------±------±------------±----------------------±--------------±--------±---------------------±-----±------------------------------------------------------------------------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±------±------------±----------------------±--------------±--------±---------------------±-----±------------------------------------------------------------------------------+| 1 | SIMPLE | cust | index\_merge | PRIMARY,name,username | name,username | 77,98 | NULL | 15 | Using sort\_union(name,username); Using where; Using temporary; Using filesort || 1 | SIMPLE | trans | ref | cust\_id | cust\_id | 8 | test\_jrazzaq.cust.id | 2235 | |±—±------------±------±------------±----------------------±--------------±--------±---------------------±-----±------------------------------------------------------------------------------+ If I understand this correctly the MySql documentation states that it cannot use indexes to do the order by in this case because:- | [B]Quote:[/B] | | You are joining many tables, and the columns in the ORDER BY are not all from the first non-constant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.) | If this is indeed the reason for the poor performance of this query, how can I change it. For example, what determines that in the ‘explain’ the ‘cust’ table is shown on row 1 and the ‘trans’ table in row 2. If they were somehow forced to be in the reverse order then the order by on the trans table would work. Any hints are appreciated. Regards Ian C. Docherty (icydee) ---
**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\ **Post date:** [November 22, 2007, 2:04pm UTC](https://forums.percona.com/t/order-by-optimisation-problems/520/2 "2007-11-22T14:04:59Z")
This might be a little better: SELECT \* FROM (select id, name, username from cust where cust.name like ‘MS%’ or username like ‘MS%’ limit 0, 10) c inner join trans on c.id = trans.cust\_id order by trans.trans\_date limit 0, 10; If the inner join limit would indeed pair down a large amount of data, then this will feed a much smaller set of values to the outer query and reduce the sort. Otherwise it won’t help. ---
**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\ **Post date:** [November 22, 2007, 2:15pm UTC](https://forums.percona.com/t/order-by-optimisation-problems/520/3 "2007-11-22T14:15:19Z")
Note: The above won’t work if the lastest transaction isn’t in one of 10 selected rows from the inner query. So it depends on what you want to accomplish. ---
**Author:** ![icydee](https://avatars.discourse-cdn.com/v4/letter/i/b38774/32.png) [@icydee](https://forums.percona.com/u/icydee)\ **Post date:** [November 22, 2007, 5:08pm UTC](https://forums.percona.com/t/order-by-optimisation-problems/520/4 "2007-11-22T17:08:00Z")
Every transaction has a customer so the inner query would not reduce the result set. I don’t see that this would help. I don’t know what influences the order of row 1 and row 2 in the ‘explain’. If I could force these to be in the opposite order then there would be no problem. Regards IcyDee ---
**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\ **Post date:** [November 22, 2007, 6:23pm UTC](https://forums.percona.com/t/order-by-optimisation-problems/520/5 "2007-11-22T18:23:27Z")
Its easy to force a different order… SELECT \* FROM cust, trans WHERE cust.id = trans.cust\_id AND ( cust.name LIKE ‘MS%’ OR cust.username LIKE ‘MS%’ ) order by trans.trans\_date limit 0,10; can become select \* from (select cust\_id, trans\_date from trans order by trans\_date) trans inner join cust on trans.cust\_id = cust.id where cust.name like ‘MS%’ OR cust.username like ‘MS%’ limit 0,10; Make an index on trans with trans\_date, cust\_id so the inner query doesn’t have to do a sort. Might work. ---
**Author:** ![icydee](https://avatars.discourse-cdn.com/v4/letter/i/b38774/32.png) [@icydee](https://forums.percona.com/u/icydee)\ **Post date:** [November 23, 2007, 4:56am UTC](https://forums.percona.com/t/order-by-optimisation-problems/520/6 "2007-11-23T04:56:18Z")
No, sorry, that gives exactly the same explain plan. ---
**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\ **Post date:** [November 23, 2007, 11:13am UTC](https://forums.percona.com/t/order-by-optimisation-problems/520/7 "2007-11-23T11:13:14Z")
try: select STRAIGHT\_JOIN … to both query variations and also adding the index ---
**Author:** ![jrabbit](https://avatars.discourse-cdn.com/v4/letter/j/a587f6/32.png) [@jrabbit](https://forums.percona.com/u/jrabbit)\ **Post date:** [December 19, 2007, 2:46pm UTC](https://forums.percona.com/t/order-by-optimisation-problems/520/8 "2007-12-19T14:46:30Z")
You probably need the two colum index mike suggests other way around - cust\_id first then trans\_date, as myqsl will need to join on the cust\_id before it sorts on the date.