Order by optimisation problems

MySQL & MariaDB Other MySQL® Questions
1

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)

2

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.

3

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.

4

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

5

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.

6

No, sorry, that gives exactly the same explain plan.

7

try:

select STRAIGHT_JOIN …

to both query variations
and also adding the index

8

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.