MySQL: Why does an Order By ID runs much slower than Order By other Columns?

Question

I am using MySQL version 5.5.14 to run the following query, QUERY 1, from a table of 5 Million rows:

SELECT P.ID, P.Type, P.Name, P.cty
     , X(P.latlng) as 'lat', Y(P.latlng) as 'lng'
     , P.cur, P.ak, P.tn, P.St, P.Tm, P.flA, P.ldA, P.flN
     , P.lv, P.bd, P.bt, P.nb
     , P.ak * E.usD as 'usP' 
FROM PIG P 
  INNER JOIN EEL E 
    ON E.cur = P.cur 
WHERE act='1' 
  AND flA >= '1615' 
  AND ldA >= '0' 
  AND yr >= (YEAR(NOW()) - 100) 
  AND lv >= '0' 
  AND bd >= '3' 
  AND bt >= '2' 
  AND nb <= '5' 
  AND cDate >= NOW() 
  AND MBRContains(LineString( Point(39.9097, -2.1973)
                            , Point(65.5130, 41.7480)
                            ), latlng) 
  AND Type = 'g' 
  AND tn = 'l' 
  AND St + Tm - YEAR(NOW()) >= '30' 
HAVING usP BETWEEN 300/2 AND 300 
ORDER BY ak
LIMIT 100;

Using an Index (Type, tn, act, flA), I am able to obtain results within 800ms. In QUERY 2, I changed the ORDER BY clause to lv, I am also able to obtain results within similar timings. In QUERY 3, I changed the ORDER BY clause to ID and the query time slowed dramatically to a full 20s on an average of 10 trials.

Running the EXPLAIN SELECT statement produces exactly the same query execution plan:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: P
         type: range
possible_keys: Index
          key: Index
      key_len: 6
          ref: NULL
         rows: 132478
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: E
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: BS.P.cur
         rows: 1
        Extra:

My question is: why does ordering by ID in QUERY 3 runs so slow compared to the rest?

The partial table definition is as such:

CREATE TABLE `PIG` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lv` smallint(3) unsigned NOT NULL DEFAULT '0',
  `ak` int(10) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`),
  KEY `id_ca` (`cty`,`ak`),
  KEY `Index` (`Type`, `tn`, `act`, `flA`),
) ENGINE=MyISAM AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1

CREATE TABLE `EEL` (
  `cur` char(3) NOT NULL,
  `usD` decimal(11,10) NOT NULL,
  PRIMARY KEY (`cur`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

UPDATE: After extensive testing of various ORDER BYs options, I have confirmed that the ID column which happens to be the Primary Key is the only one causing the slow query time.

not an answer, but a comment on your queries/database. Use relevant names, for your and others sake. If you leave this application for a month or so, and then come back. You will have to figure out what every column contains. And for questions on forums like this it doesn't make much sense. It will require a little extra typing, but that should not be any problem. — Rene Pot, Sep 28 '11 at 16:12
@ Topener: I am sorry if using short-forms on column names makes it harder for you to understand my question. I have my concern in revealing the application. Hope you can understand ;p — Question Overflow, Sep 29 '11 at 07:54
How much time does the query need to run without `ORDER BY` but with `LIMIT 100` ? — ypercubeᵀᴹ, Sep 29 '11 at 16:27
It takes a few hundred milliseconds on average to run without sorting. — Question Overflow, Sep 29 '11 at 16:38
And how many rows does your query return when you have no `LIMIT` ? — ypercubeᵀᴹ, Sep 29 '11 at 17:01
I just stumbled about this and am surprised that the use of HAVING without a GROUP BY condition is even legal. Thats completely news to me. Does this the same as a WHERE clause? — radiospiel, Sep 30 '11 at 07:35
And I am surprised that ordering a mere 250 rows would take ~20 secs. Could you put the query w/o the ORDER BY column in a subquery, and then SELECT from that? (And on a sidenote I heard a rumor that INNER JOINs can be slower than LEFT JOINs with an additional foreign_id IS NOT NULL condition.) — radiospiel, Sep 30 '11 at 07:41
@ radiospiel, yes, `HAVING` can be used without `GROUP BY`. You can create a separate question on Stackoverflow rather than posting it here. — Question Overflow, Oct 01 '11 at 03:45

score 4 · Answer 1 · answered Sep 29 '11 at 21:08

From MySQL documentation at http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

. . .

The key used to fetch the rows is not the same as the one used in the ORDER BY:

`SELECT * FROM t1 WHERE key2=constant ORDER BY key1;`

This probably won't help, but what happens if you add AND ID > 0 to the WHERE clause? Would this cause MySQL to use the primary key for sorting? Worth a try I suppose.

(It seems odd that ordering with ak is efficient, since ak does not even have an index, but that may be due to fewer values for ak?)

@ Bob: I have done the test. The query execution plan does not change by adding `AND ID > 0` to `WHERE` clause. The number of rows returned should be the same regardless of the sorting column. MySQL does not perform significantly slower with `ak` as only a few hundred rows are returned. — Question Overflow, Sep 30 '11 at 05:12

score 1 · Answer 2 · answered Jul 14 '14 at 13:45

1

you can use force index(PRIMARY) try it, and you will see in explain query that mysql now will use the primary key index when 'order by'

answered Jul 14 '14 at 13:45

Eyal Ch

9,552
5
44
54

score 1 · Answer 3 · answered Sep 28 '11 at 19:05

1

If the condition in the WHERE clause differs from the one in the ORDER BY or it is not part of a composite index, then the sorting does not take place in the storage engine but rather at the MySQL server level which is much slower. Long story short you must rearrange your indexes in order to satisfy both the row filtering and the sorting as well.

answered Sep 28 '11 at 19:05

georgepsarakis

1,927
3
20
24

For QUERY 1, `ORDER BY` contains ak, which is not in the where clause and also not in the composite index. For QUERY 2, `ORDER BY` contains lv, which is in the where clause but not in the composite index. Both QUERIES 1 and 2 are fast. Your explanation does not seem to answer my question. – Question Overflow Sep 29 '11 at 07:59

MySQL: Why does an Order By ID runs much slower than Order By other Columns?

3 Answers3

Linked