MySQL optimization - weird simple situation - ORDER by LIMIT
Posted: Wed May 19, 2010 3:28 pm
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.
Here`s my simple table:
Indexes:
Thanks for any help.
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)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)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)