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