MySQL Performance: MySQL => Order by optimisation problems

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)

Report message to a moderator