Re: Optimizer not working adequately

[Björn Steinbrink]

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.