The “DBMS” part of MySQL is fine – storage engines (especially
with new kids on the block), replication, etc, but
it really sucks at executing SQL.
I won’t even get into complex SQL that has complex data
dependencies, will start with very basic tasks.
SELECT * FROM table WHERE indexed = "A" LIMIT 1
If multiple indexes can satisfy the query, MySQL will hit each of
them at least twice – looking up first “A” and last “A” records.
It will do that while planning the SQL execution. Once it comes
up with a plan, it will go and hit the index it picked again,
this time once. So, if you have two candidate indexes, you will
have 5 index accesses at 4 positions.
How would a direct cursor access work? Single index hit. Want to
simulate that in SQL? You can add a ‘FORCE INDEX’ hint, then only
one …
[Read more]