Page 1 of 1

MySQL optimization - weird simple situation - ORDER by LIMIT

Posted: Wed May 19, 2010 3:28 pm
by freamer
Hello everyone,
I`m barely stuck...

SELECT id FROM msg ORDER BY id DESC LIMIT 1;

Should this simple query require a full scan,as explain below shows or am I doing something wrong?
Even if I remove ORDER by it still gives me 6526 rows.
In my understandings explain should give me 1 row,as stated by mysql docs.

Code: Select all

mysql> EXPLAIN SELECT id FROM msg ORDER BY id DESC LIMIT 1,1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | msg   | index | NULL          | PRIMARY | 4       | NULL | 6526 | Using index | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
Here`s my simple table:

Code: Select all

DESCRIBE msg;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| to    | int(20)          | NO   | MUL | NULL    |                | 
| from  | int(20)          | NO   |     | NULL    |                | 
| date  | int(50)          | NO   |     | NULL    |                | 
| read  | tinyint(1)       | NO   |     | NULL    |                | 
| msg   | longtext         | NO   |     | NULL    |                | 
+-------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
Indexes:

Code: Select all

SHOW INDEX FROM msg;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| msg   |          0 | PRIMARY  |            1 | id          | A         |        6526 |     NULL | NULL   |      | BTREE      |         | 
| msg   |          1 | to_read  |            1 | to          | A         |           2 |     NULL | NULL   |      | BTREE      |         | 
| msg   |          1 | to_read  |            2 | read        | A         |           2 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
Thanks for any help.

Re: MySQL optimization - weird simple situation

Posted: Wed May 19, 2010 3:50 pm
by Eran
The EXPLAIN is correct, since you are not using any filtering criteria (LIMIT does not count for this), all the rows in the table are targeted by it. The LIMIT clause just causes the query to stop after 1 row, so effectively only 1 row is touched.

Re: MySQL optimization - weird simple situation

Posted: Wed May 19, 2010 4:01 pm
by freamer
Okay, so here`s the query with filtering criteria, 6522 rows are still scanned...
What`s wrong now?

Code: Select all

mysql> EXPLAIN SELECT `to`, `read` FROM `msg` WHERE `to` = 1 AND `read` = 0 LIMIT 1;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | msg   | ref  | to_read       | to_read | 5       | const,const | 6522 | Using index | 
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

Re: MySQL optimization - weird simple situation

Posted: Wed May 19, 2010 4:06 pm
by Eran
Is it possible that all rows but 4 currently have 'to'=1 and 'read'=0 ? are those the default by any chance? looking at your indexes that's a high possibility

Re: MySQL optimization - weird simple situation - ORDER by L

Posted: Wed May 19, 2010 4:15 pm
by freamer
Ah, yes. You are right,if I change some rows read to 1, explain gives me lower value.

But, anyway, it seems like the query is unoptimized as rows value is too high.
Shouldn`t it give me 1 row in this situation?

Correct me, if I`m wrong, please.

Re: MySQL optimization - weird simple situation

Posted: Wed May 19, 2010 4:23 pm
by mikosiko
Edited: because I didn't read pytrin first answer and my answer was not in the point

Re: MySQL optimization - weird simple situation - ORDER by L

Posted: Wed May 19, 2010 4:26 pm
by Eran
Again - the LIMIT clause isn't taken into effect when calculating rows in the EXPLAIN results. So there is nothing basically wrong with the query.

Re: MySQL optimization - weird simple situation - ORDER by L

Posted: Wed May 19, 2010 4:37 pm
by freamer
Thanks everyone.
I`ve finally got the point :)