471,073 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

mysql messes up ORDER BY optimisation?!



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
Jul 20 '05 #1
1 1314
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.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Bruce A. Julseth | last post: by
1 post views Thread by Boris Kroeplien | last post: by
1 post views Thread by Doug | last post: by
reply views Thread by Lenz Grimmer | last post: by
2 posts views Thread by gamalt | last post: by
2 posts views Thread by gamalt | last post: by
2 posts views Thread by Jonathan | last post: by
11 posts views Thread by DJJ | last post: by
2 posts views Thread by My SQL | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.