Just use a real database then, smth. like Postgres or Oracle etc.

It is generally bad practice to use SELECT * in any query. In some RDBMS, the columns specified in the SELECT are considered by the optimizer when it identifies indexes for execution plans. I know this is a fact with SQL Server. I would retry your query only specifying the columns that are needed by the application and see if the MySQL optimizer comes up with a different execution plan.

To answer your question – no, MySQL is not a toy. I would suggest that you have a read through section 7.4.5 of the manual as to why you are seeing this particular behaviour (http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html):

“Sometimes MySQL will not use an index, even if one is available. One way this occurs is when the optimizer estimates that using the index would require MySQL to access a large percentage of the rows in the table. (In this case, a table scan is probably much faster, because it will require many fewer seeks.) However, if such a query uses LIMIT to only retrieve part of the rows, MySQL will use an index anyway, because it can much more quickly find the few rows to return in the result.

Hash indexes have somewhat different characteristics than those just discussed:
* They are used only for = or comparisons (but are very fast).
* The optimizer cannot use a hash index to speed up ORDER BY operations. (This type
of index cannot be used to search for the next entry in order.)
* MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM table to a hash-indexed MEMORY table.
* Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)”

I would suggest that sorting 100000 rows would probably in the manual’s terms “require MySQL to access a large percentage of the rows in the table”. Have a read through the order-by optimization section of the manual and you will probably find a way to instruct it to do what you want (http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html).

Jokub:

I agree with you: sorting 1M (all) rows requires “to access a large percentage of the rows in the table”. But anyway, how can you explain the fact that the query takes 3 secs if doesn’t involves the last row and 1 minute if it does? (despite of the filesort).

I must insist: If I force the optimizer to use the index, the query performs well.

I do, however, agree that this sounds like a bug in the optimizer:

“But the problem is still there: If the limit reaches (or surpases) the last row, then the optimizer throws away the index and generates a filesort (taking more than 1 minute, making the query totally useless…)”

No, MySQL isn’t a toy… but it’s not a serious tool either. It still can be the right tool for the job, just as long as the people using the tool understand the problems they’ll face.

The optimizer is horrid, and the query planner is a joke. Correlated subqueries, anybody? The most complex query in my employer’s codebase is only so complex because it’s designed to work around how utterly braindead the MySQL query planner is. I’ve even encountered times when FORCE INDEX() won’t actually force the use of that index, if the optimizer thinks that using another index will be faster.

Honestly though, if it wasn’t for the out-of-the-box it-just-works replication, I’d have switched to something else ages ago.

Hi,
I don’t know if I’ll be able to link here, but Shlomi Noach goes into some detail on this issue – and in his case he’s using a WHERE clause to restrict the number of rows, yet the optimizer isn’t using the index as the ORDER BY clause invokes the primary key. So it’s not just the “select *” part which is causing problems.

http://code.openark.org/blog/mysql/7-ways-to-convince-mysql-to-use-the-right-index

Once you’re aware of this as a developer or administrator, you can take relevant action. I’m still happy to use Mysql on my sites; well until most hosts provide PostgreSQL. Having used both Oracle and SQL*Server in the past, I’m well aware that there are gotchas and glitches with all of them. Well, ok, too many on Mysql, but with luck that’ll provide interesting subjects for my blog.

Saludos desde Inglaterra

DBMark

Thank you for these comments. I was having the same problem. I have only a few thousand records and it blows chunks even with an index. It seems to not be fetching in the background and returning the first row immediately, like Oracle does when it uses nested loops.

For users who just want to feel like the database is responsive, the first page needs to come back fast, as well as paginate quickly on through. Its counterintuitive for it to be otherwise. The first page of an encyclopedia is just as accessible as the first page of a baby book.