Announcement Module
No announcement yet.

Order by optimisation problems

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Order by optimisation problems

    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 = trans.cust_id AND ( 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 | | 2235 | |+----+-------------+-------+-------------+-----------------------+---------------+---------+----------------------+------+-------------------------------------------------------------------------------+

    If I understand this correctly the MySql documentation states that it cannot use indexes to do the order by in this case because:-


    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.

    Ian C. Docherty (icydee)

  • #2
    This might be a little better:

    SELECT *
    (select id, name, username from cust
    where like 'MS%' or username like 'MS%'
    limit 0, 10) c
    inner join trans on = 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.



        • #5
          Its easy to force a different order...

          SELECT *
          FROM cust, trans
          WHERE = trans.cust_id
          AND (
          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 =
 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

              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.