1

I have a table with a primary key consisting of two integer columns. I store ~200 millions lines in this table. My program needs to get the full table ordered by the columns of the primary key in the same order.

So, my query looks like Select * from MYTABLE order by PK1,PK2;

I noticed that instead of sending data immediately, the query spend a lot of time in the "sorting result" state. I would have thought that mysql would use the primary key index to access the data directly in the required order. But, it seems it scans it without order and then sorts the data afterward.

Is it possible to change the behavior and make the query more efficient ?

Remark : I have tried using mysql 5.5 and mariadb10.2.

Here are some (I Hope) useful information (I have tried to EXPLAIN some orders using indexed and non-indexed columns and all give the same result)

mysql> show indexes from reponse;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| reponse |          0 | PRIMARY     |            1 | objet_id    | A         |      502989 |     NULL | NULL   |      | BTREE      |         |               |
| reponse |          0 | PRIMARY     |            2 | question_id | A         |   189627157 |     NULL | NULL   |      | BTREE      |         |               |
| reponse |          1 | idxQuestion |            1 | question_id | A         |        5340 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0,30 sec)

mysql> explain select * from reponse order by objet_id,question_id;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627158 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,23 sec)

mysql> explain select * from reponse order by question_id,objet_id;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627159 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,44 sec)

mysql> explain select * from reponse order by objet_id,question_id;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627159 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,11 sec)

mysql> explain select * from reponse order by n_pose0;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627175 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,26 sec)

mysql> explain select * from reponse order by question_id;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627421 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,12 sec)

mysql> select version();
+--------------------------------------+
| version()                            |
+--------------------------------------+
| 10.2.12-MariaDB-10.2.12+maria~jessie |
+--------------------------------------+

The table REPONSE was created like this :

    CREATE TABLE `reponse` (
  `objet_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `n_pose0` int(11) NOT NULL,
  `n_pose1` int(11) NOT NULL,
  `n_pose2` int(11) NOT NULL,
  `n_pose3` int(11) NOT NULL,
  `n_pose4` int(11) NOT NULL,
  PRIMARY KEY (`objet_id`,`question_id`),
  KEY `idxQuestion` (`question_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

In complement to the given answer, you can add an hint for forcing the engine to use an index. I tested it and the response time has doubled... It seems the optimizer did a good job this time :

date;mysql --quick -e 'select * from reponse order by objet_id,question_id' > /dev/null;date
Tue Nov 20 15:49:22 CET 2018
Tue Nov 20 15:52:19 CET 2018
date;mysql --quick -e 'select * from reponse force index(PRIMARY) order by objet_id,question_id' > /dev/null;date
Tue Nov 20 15:52:45 CET 2018
Tue Nov 20 15:58:23 CET 2018
  • Why can't your program do the sorting itself? It will likely be more efficient for your program to sort the data after it gets it. – Joe W Nov 19 '18 at 13:47
  • I wondered whether there should be a sorting at all as an index induces a sorting. For instance a map in C++ enables you to iterate over your elements in the order of the keys – Arnaud Mégret Nov 19 '18 at 14:49
  • 2
    Is the compound primary key in the same order, PK1 followed by PK2? What does EXPLAIN {query} show? It might be your MySQL version has a bug and this is fixed in later versions. Getting the database to do database things right, like sorting and using indexes is significantly better than writing your own. MySQL tables are order by the primary key in a B+ tree. – danblack Nov 20 '18 at 1:14
  • 1
    Aside, why are you fetching 200 million lines? This sounds like and odd thing to do (except for a backup). Maybe there's a better way. – danblack Nov 20 '18 at 1:15
  • 1
    Hi. This is a faq, google it. Tables have no order. Select statements with order by have ordered results. Implementations have order but that does not determine query result order. Efficient implementation requires indexes. Indexes can be ordered. Read all the mentions of constraints & indexes in the manual. Also--please show all your DDL & EXPLAIN output. – philipxy Nov 20 '18 at 3:55
2

ENGINE=MyISAM, that's the reason for the chosen query plan.

If the table was using the InnoDB engine, then the primary key index would be the clustered key of the table, so it wouldn't have to do sorting after reading it, as the query's ORDER BY matches the primary key.

With MyISAM it has two options: to read the index and the (heap) table (without sorting) or only the table and do a sort. It chooses the second because the optimizer thinks it's faster.

  • Thanks! Is there an hint that would force the optimizer to chose the other alternative ? I have noticed that sometimes, it doesn't chose the best strategy. – Arnaud Mégret Nov 20 '18 at 13:51
  • 1
    I suggest you use InnoDB engine. MyISAM is only there because it used to be the default engine a decade ago. Almost all active development in MySQL and MariaDB is done in InnoDB engine. It also has many features that MyISAM lacks, like FOREIGN KEY support and transactions. – ypercubeᵀᴹ Nov 20 '18 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.