MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizer not working adequately
Posted by: Björn Steinbrink
Date: April 26, 2006 03:36AM

In the first case, MySQL can just read the ids directly from the index, as all needed values are stored in it anyway, scanning those values is fast.
In the second case, MySQL has to actually read data from the table, as there is no index that contains both values. Also, it cannot use any index as there is no condition and no ordering. So MySQL scans the table until it has enough rows. 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.
Adding "ORDER BY id" should give that query a boost IIRC.

Options: ReplyQuote

Written By
April 25, 2006 07:41PM
Re: Optimizer not working adequately
April 26, 2006 03:36AM
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.