| Order by optimisation problems [message #2187] |
Fri, 09 November 2007 10:25  |
 icydee
Messages: 3
Registered: November 2007 |
Junior Member |
|
|
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:-
| Quote: |
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)
|
|
|
|
|
|
|
|
|
| Re: Order by optimisation problems [message #2406 is a reply to message #2263 ] |
Wed, 19 December 2007 15:16  |
jrabbit
Messages: 13
Registered: December 2007 |
Junior Member |
|
|
|
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.
|
|
|
Members
Search
Help
Register
Login
Home
