Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
No Index? Or am I misinterpreting the explain plan? –  TomTom Jul 23 '13 at 18:15
    
I updated the post with information about the table and indexes on said table. You're not misinterpreting the explain plan readout, it just not listing the primary key as an index there for some reason.. –  Hawkwing Jul 23 '13 at 18:35
add comment

1 Answer

up vote 4 down vote accepted

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:

EXPLAIN SELECT id,category_id,sha FROM incidents LIMIT 5 OFFSET 150000;

If you put an ORDER BY clause 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":

SELECT *
FROM my_table
WHERE id BETWEEN 100000 AND 100005

That way the query plan can use an index to jump to that specific sub section of the table immediately.

share|improve this answer
    
Interesting. I'm surprised there's a distinction there... I can't think of a reason why you would ever want it to behave the way it does with the LIMIT X OFFSET Y syntax. At any rate, this is very useful to know. –  Hawkwing Jul 23 '13 at 19:40
1  
It makes no sense to add a LIMIT X OFFSET Y clause if you don't have an ORDER BY clause. Otherwise there's no consistency in what offset of rows are being fetched. It's just an arbitrary offset! –  sehrope Jul 23 '13 at 19:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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