MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizer not working adequately
Posted by: EM Ayllon
Date: April 26, 2006 09:43AM

"As the name field is varchar, there's no way for MySQL to directly know the address of any row, so for LIMIT 30000,10 it actually has to scan the table from row 0 to row 30009 to find the requested rows. "

Why not? In the first instance MySQL found row 30000 to 300010 without needing any info from 'name', in fact as you say, without even reading data from table. MySQL should be able to locate those rows in the same fashion as in the first query and then obtain the 'name' info by reading data from those particular rows. Can not see MySQL being 'very clever' if it can not work out something as simple as this.

As for the increase in performance by adding an 'order' it actually does not seem to be a 'boost' but rather just a little help:

Without order: 0.58 secs
with order: 0.45 secs

Explain shows that it still considers all rows in the table:

explain select id,name from people order by id limit 30000,10 ;
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------+
| 1 | SIMPLE | people| index | NULL | PRIMARY | 4 | NULL | 42003 | |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------+

Options: ReplyQuote


Subject
Views
Written By
Posted
2500
April 25, 2006 07:41PM
Re: Optimizer not working adequately
1793
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.