Bug #1942 Slow queries with "order by"
Submitted: 25 Nov 2003 1:13 Modified: 17 Mar 2004 9:43
Reporter: Christian Gruber Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.16 OS:Linux (Linux)
Assigned to: Heikki Tuuri CPU Architecture:Any

[25 Nov 2003 1:13] Christian Gruber
Description:
Hello!

Selecting from InnoDB tables becomes really slow when the rows are big
(in our case on avg 128MB in one longtext column) and the result is
ordered. The longtext column is not in the list of selected and
ordered columns (see example below).

When performing the statement order with the "order by" clause, the
disk I/O rate of the server increases significantly. It seems that
without the "order by", only the selected columns of the rows are read
from disk, while the whole row is selected when the "order by" is
used.

We are running MySQL 4.0.16 on SuSE Linux 7.1. The DB server is a
Pentium III with 700MHz and has 800MB RAM.

Greetings,
Christian Gruber

---------8<--------------8<--------------8<--------------8<----------

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql> desc contig;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int(11)      |      | PRI | 0       |       |
| contiglist_id | int(11)      | YES  | MUL | NULL    |       |
| contigtype_id | int(11)      |      |     | 0       |       |
| code          | varchar(255) | YES  | MUL | NULL    |       |
| create_time   | int(11)      |      |     | 0       |       |
| contig_start  | int(11)      |      |     | 0       |       |
| contig_stop   | int(11)      |      |     | 0       |       |
| contig_length | int(11)      |      |     | 0       |       |
| description   | text         | YES  |     | NULL    |       |
| sequence      | longtext     | YES  |     | NULL    |       |
| quality       | double       | YES  |     | NULL    |       |
| version       | int(11)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
12 rows in set (0.08 sec)

mysql> select min(contig_length), max(contig_length), avg(contig_length) from contig;
+--------------------+--------------------+--------------------+
| min(contig_length) | max(contig_length) | avg(contig_length) |
+--------------------+--------------------+--------------------+
|           61093376 |          195869683 |     128863053.7000 |
+--------------------+--------------------+--------------------+
1 row in set (0.12 sec)

mysql> SELECT id, code, contig_length, description FROM contig;
+----+-------------+---------------+---------------------+
| id | code        | contig_length | description         |
+----+-------------+---------------+---------------------+
|  0 | Mouse:chr01 |     195869683 | Mouse chromosome 1  |
|  1 | Mouse:chr02 |     181423755 | Mouse chromosome 2  |
|  2 | Mouse:chr03 |     160674399 | Mouse chromosome 3  |
|  3 | Mouse:chr04 |     152921959 | Mouse chromosome 4  |
|  4 | Mouse:chr05 |     149719773 | Mouse chromosome 5  |
|  5 | Mouse:chr06 |     149950539 | Mouse chromosome 6  |
|  6 | Mouse:chr07 |     134401573 | Mouse chromosome 7  |
|  7 | Mouse:chr08 |     128923138 | Mouse chromosome 8  |
|  8 | Mouse:chr09 |     124467299 | Mouse chromosome 9  |
|  9 | Mouse:chr10 |     130738012 | Mouse chromosome 10 |
| 10 | Mouse:chr11 |     122862689 | Mouse chromosome 11 |
| 11 | Mouse:chr12 |     114462600 | Mouse chromosome 12 |
| 12 | Mouse:chr13 |     116242670 | Mouse chromosome 13 |
| 13 | Mouse:chr14 |     115844145 | Mouse chromosome 14 |
| 14 | Mouse:chr15 |     104111694 | Mouse chromosome 15 |
| 15 | Mouse:chr16 |      98986639 | Mouse chromosome 16 |
| 16 | Mouse:chr17 |      93529596 | Mouse chromosome 17 |
| 17 | Mouse:chr18 |      91041441 | Mouse chromosome 18 |
| 18 | Mouse:chr19 |      61093376 | Mouse chromosome 19 |
| 21 | Mouse:chrX  |     149996094 | Mouse chromosome X  |
+----+-------------+---------------+---------------------+
20 rows in set (0.04 sec)

mysql> SELECT id, code, contig_length, description FROM contig order by code;
+----+-------------+---------------+---------------------+
| id | code        | contig_length | description         |
+----+-------------+---------------+---------------------+
|  0 | Mouse:chr01 |     195869683 | Mouse chromosome 1  |
|  1 | Mouse:chr02 |     181423755 | Mouse chromosome 2  |
|  2 | Mouse:chr03 |     160674399 | Mouse chromosome 3  |
|  3 | Mouse:chr04 |     152921959 | Mouse chromosome 4  |
|  4 | Mouse:chr05 |     149719773 | Mouse chromosome 5  |
|  5 | Mouse:chr06 |     149950539 | Mouse chromosome 6  |
|  6 | Mouse:chr07 |     134401573 | Mouse chromosome 7  |
|  7 | Mouse:chr08 |     128923138 | Mouse chromosome 8  |
|  8 | Mouse:chr09 |     124467299 | Mouse chromosome 9  |
|  9 | Mouse:chr10 |     130738012 | Mouse chromosome 10 |
| 10 | Mouse:chr11 |     122862689 | Mouse chromosome 11 |
| 11 | Mouse:chr12 |     114462600 | Mouse chromosome 12 |
| 12 | Mouse:chr13 |     116242670 | Mouse chromosome 13 |
| 13 | Mouse:chr14 |     115844145 | Mouse chromosome 14 |
| 14 | Mouse:chr15 |     104111694 | Mouse chromosome 15 |
| 15 | Mouse:chr16 |      98986639 | Mouse chromosome 16 |
| 16 | Mouse:chr17 |      93529596 | Mouse chromosome 17 |
| 17 | Mouse:chr18 |      91041441 | Mouse chromosome 18 |
| 18 | Mouse:chr19 |      61093376 | Mouse chromosome 19 |
| 21 | Mouse:chrX  |     149996094 | Mouse chromosome X  |
+----+-------------+---------------+---------------------+
20 rows in set (2 min 12.20 sec)

mysql> show table status like 'contig';
+--------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+-------------------------+
| Name   | Type   | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment                 |
+--------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+-------------------------+
| contig | InnoDB | Dynamic    |   20 |      129316454 |  2586329088 |            NULL |        32768 |         0 |           NULL | NULL        | NULL        | NULL       |                | InnoDB free: 5737472 kB |
+--------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+-------------------------+
1 row in set (0.04 sec)

How to repeat:
Create an InnoDB table with a longtext column and other columns and fill it with ca 20 rows with ca 128MB each. Perform the given select statements.

Suggested fix:
???
[25 Nov 2003 1:28] Heikki Tuuri
Hi!

This is a known problem. Since MySQL uses the row 'ref' (normally, the PRIMARY KEY)in the ORDER BY, but does not mark the columns that it needs with

field->query_id == thd->query_id

InnoDB decides to play safe and fetch ALL columns in a row. In the case of TEXT or BLOB columns that can take a lot of time.

Possible fix: in an ORDER BY it is probably enough to fetch only PRIMARY key columns in this case. The fix may come in some version 4.1.x.

Regards,

Heikki
[16 Mar 2004 15:51] Miguel Solorzano
[25 Nov 2003 1:28am] Heikki Tuuri

.................

Possible fix: in an ORDER BY it is probably enough to fetch only PRIMARY key
columns in this case. The fix may come in some version 4.1.x.

Heikki, What is the status of the above ?
[17 Mar 2004 1:18] Christian Gruber
Is there any chance to have the change already in some 4.0.x version? I think it will take still some time until 4.1.x is usable in a production environment, and it would be nice if the defect would be fixed in an earlier version... :-)

Greetings,
Christian
[17 Mar 2004 9:43] Heikki Tuuri
Hi!

I have now fixed this to 4.1.2. I do not dare to backport the fix to 4.0.

Regards,

Heikki
[18 Mar 2004 0:28] Christian Gruber
Hm. Too bad it's not in 4.0... But anyway, thanks for the fix.

Christian