MySQL Forums
Forum List  »  Optimizer & Parser

Optimizer not working adequately
Posted by: emilio.ayllon
Date: April 25, 2006 07:41PM

I am investigating something as simple as this:

SELECT id FROM people LIMIT 30000,10;
where id is the primary key

which with EXPLAIN gives something like this:

+----+-------------+------------+-------+---------------+---------+---------+---
---+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | re
f | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+---
---+-------+-------------+
| 1 | SIMPLE | people | index | NULL | PRIMARY | 4 | NU
LL | 41988 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+---
---+-------+-------------+
1 row in set (0.00 sec)

Now, if I modify a little the expression:


SELECT id,name FROM people LIMIT 30000,10;
name being varchar(50)

+----+-------------+------------+------+---------------+------+---------+------+
-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+
-------+-------+
| 1 | SIMPLE | people | ALL | NULL | NULL | NULL | NULL |
41988 | |
+----+-------------+------------+------+---------------+------+---------+------+
-------+-------+

What I have observed is that with the second the query becomes much slower because it actually checks the total number of rows on the table according to the explanation above. I have also observed that the query becomes slower when the offset increases, i.e. limit 0, 10 is much faster than limit 30000,10 which is faster thatn limit 40000,10 and so on....

Can not see the reason why mysql does not optimize this type of query.

Regards,

Emilio.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizer not working adequately
2501
April 25, 2006 07:41PM
1795
April 26, 2006 09:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.