MySQL optimization - weird simple situation - ORDER by LIMIT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
freamer
Forum Newbie
Posts: 5
Joined: Mon Oct 05, 2009 3:08 am

MySQL optimization - weird simple situation - ORDER by LIMIT

Post 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.
Last edited by freamer on Wed May 19, 2010 4:07 pm, edited 1 time in total.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL optimization - weird simple situation

Post 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.
freamer
Forum Newbie
Posts: 5
Joined: Mon Oct 05, 2009 3:08 am

Re: MySQL optimization - weird simple situation

Post 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)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL optimization - weird simple situation

Post 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
freamer
Forum Newbie
Posts: 5
Joined: Mon Oct 05, 2009 3:08 am

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

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL optimization - weird simple situation

Post by mikosiko »

Edited: because I didn't read pytrin first answer and my answer was not in the point
Last edited by mikosiko on Wed May 19, 2010 4:42 pm, edited 1 time in total.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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.
freamer
Forum Newbie
Posts: 5
Joined: Mon Oct 05, 2009 3:08 am

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

Post by freamer »

Thanks everyone.
I`ve finally got the point :)
Post Reply