Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 717
Question Overflow Avatar asked Sep 28 '11 08:09

Question Overflow


People also ask

Does order matter in MySQL?

So the order of columns in a multi-column index definitely matters. One type of query may need a certain column order for the index. If you have several types of queries, you might need several indexes to help them, with columns in different orders.

Does index help in ORDER BY MySQL?

Yes, MySQL uses your index to sort the information when the order is by the sorted column. Also, if you have indexes in all columns that you have added to the SELECT clause, MySQL will not load the data from the table itself, but from the index (which is faster).

Why is ORDER BY used in MySQL?

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.


1 Answers

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?)

like image 165
Bob Johnson Avatar answered Sep 28 '22 10:09

Bob Johnson