mysql messes up ORDER BY optimisation?!

[Doug]

I have a pretty long query that ends with

ORDER BY R.r_recent_hits DESC LIMIT 0, 1

I also have an index on R.r_recent_hits. I did an explain select and
got this:

ALL - which means (from the manual):

A full table scan will be done for each combination of rows from the
previous tables. This is normally not good if the table is the first
table not marked const, and usually very bad in all other cases.

My question is, why in the world would MySql choose to do this type of
join when there is a perfectly good index there! And I have run analyse
on all of the tables.

It seems like it would be a no-brainer, as soon as the optimizer sees
ORDER BY, just use the given index for that column, that will get you
the highest value easily. Does any one know why it would not
automatically do that? The query essentially does nothing but selects
the highest value. If i suggest what index to use by saying USE INDEX,
it works fine. But, I don't think i should have to.

if you are interested, here is the query:

EXPLAIN SELECT H.accumulated_hits, R.num_messages, R.r_recent_hits,
T.topic_scope, R.thread_id, U.username, U.user_id, T.title,
UNIX_TIMESTAMP(T.post_time) AS post_time, T.topic_id, T.description FROM
threads as R, topics AS T , users AS U, hit_counter AS H WHERE
T.topic_id=R.topic_id AND H.object_type='THREAD' AND
H.object_id=R.thread_id AND U.user_id=T.user_id ORDER BY R.r_recent_hits
DESC LIMIT 0, 1

-d

[Bill Karwin]

Doug wrote:

My question is, why in the world would MySql choose to do this type of
join when there is a perfectly good index there! And I have run analyse
on all of the tables.

According to the MySQL manual, it should be able to use an index for a
DESC sort, as long as you don't also have an ASC sort based on the same
table.

It could be that it had to do a table scan on the table anyway, because
of your other conditions in the WHERE clause or the join conditions.
Once it determines that a full table scan is necessary, it might as well
skip the use of the index, which is now effectively extra work.

Do you have indexes defined on threads.topic_id and thread.thread_id?
You might also benefit from indexes on topics.topic_id,
hit_counter.object_type, hit_counter.object_id, users.user_id, and
topics.user_id.

See also:
http://dev.mysql.com/doc/mysql/en/OR...imization.html

Regards,
Bill K.