I have a mysql innodb database running with some 200k records, and I'm having some unexpected slowdown when accessing records near the end of the database. Specifically, it is taking .10 seconds to perform a select query on these later entries, while queries against entries at the front of the database execute in .001 seconds. I have tested this numerous times, and the time to access seems to increase linearly as the position of the element being accessed increases.
For example:
EXPLAIN SELECT id,category_id,sha FROM incidents LIMIT 5 OFFSET 150000;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | incidents | ALL | NULL | NULL | NULL | NULL | 179079 | |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+`
Resulted in a running time of .11 (within 10% of my average from the tests I've run)
Result below is the result 'show profile', not the results of running the query itself.
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000203 |
| Opening tables | 0.000026 |
| System lock | 0.000006 |
| Table lock | 0.000012 |
| init | 0.000026 |
| optimizing | 0.000006 |
| statistics | 0.000014 |
| preparing | 0.000011 |
| executing | 0.000003 |
| Sending data | 0.100825 |
| end | 0.000016 |
| query end | 0.000002 |
| freeing items | 0.000259 |
| logging slow query | 0.000003 |
| cleaning up | 0.000004 |
+--------------------+----------+
Meanwhile this Query:
EXPLAIN SELECT id,category_id,sha FROM incidents LIMIT 5 OFFSET 10;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | incidents | ALL | NULL | NULL | NULL | NULL | 179079 | |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
Resulted in a running time of .0004 seconds
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000100 |
| Opening tables | 0.000020 |
| System lock | 0.000007 |
| Table lock | 0.000009 |
| init | 0.000025 |
| optimizing | 0.000006 |
| statistics | 0.000013 |
| preparing | 0.000011 |
| executing | 0.000003 |
| Sending data | 0.000318 |
| end | 0.000008 |
| query end | 0.000003 |
| freeing items | 0.000022 |
| logging slow query | 0.000003 |
| cleaning up | 0.000002 |
+--------------------+----------+
For further context, here is the table I am running these queries on:
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| summary | text | YES | | NULL | |
| sha | varchar(80) | YES | | NULL | |
| recvtime | datetime | YES | MUL | NULL | |
| trigtime | datetime | YES | MUL | NULL | |
| category_id | int(10) unsigned | YES | MUL | NULL | |
| xml | varchar(30) | YES | | NULL | |
| severity | varchar(30) | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
As well as indexes on the table
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| incidents | 0 | PRIMARY | 1 | id | A | 179079 | NULL | NULL | | BTREE | |
| incidents | 1 | incident_recvtime | 1 | recvtime | A | 89539 | NULL | NULL | YES | BTREE | |
| incidents | 1 | incident_trigtime | 1 | trigtime | A | 1 | NULL | NULL | YES | BTREE | |
| incidents | 1 | incident_category_id | 1 | category_id | A | 1 | NULL | NULL | YES | BTREE | |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Its obvious from looking at the numbers that the increase comes from the "sending data" portion of the request, but that makes no sense, as this entire table is assembled from copies of the same data and is of identical size.
This seems like it would cripple databases with a large number of rows, especially since the time these queries take is increasing with both the volume of the data stored in the average row as well as with the number of rows between the row you're trying to access and the row at offset 0.
Is the database storing these values in a linked list? Is there any setting I can change to alter this behavior?
Best Answer
This is because you're asking it to skip the first 150000 records. It has to read through all of them first before it gets to the 5 that you actually want:
If you put an
ORDER BYclause then it's possible that it could jump to end of the index (if it knows how many there are in it) or at least do an index skip scan to jump through it faster (vs sequential scan on the table rows). Either way though there would be more work involved then just getting the first couple records.To do something like this efficiently you should specify an exact range that you want to query rather than just saying "#150000 - #150005":
That way the query plan can use an index to jump to that specific sub section of the table immediately.