I'm doing a select to grab some rows, and I need to select the 100 newest ones, so I'm doing a sort on an integer timestamp field.
Anyway, my query looks something like this (I've simplified it just to show the concept):
Now, the query works fine, but as far as I'm concerned, it's slow. With about 150,000 records in the content table, it takes about .4 seconds. By tweaking the indexes, I can get that time down a little, but that seems to effect other stuff. But, by removing the ORDER BY, it goes down to a very respectable 0.0013.
SELECT c.content_id, c.content_user_id, c.content_client_timestamp, u.user_username
FROM tbl_content as c, tbl_user_relationships as r, tbl_users as u
WHERE r.relationship_user_id = VARIABLE_USER_ID_HERE && c.content_user_id = r.relationship_to_user_id && u.user_id = c.content_user_id
ORDER BY c.content_client_timestamp DESC LIMIT 100
My indexes are like this: I of course have all the fields that are joined on indexed, and I have content_client_timestamp indexed. It seems like it's not properly using that index to order by. I tried doing an index like content_client_timestamp, content_user_id, and that brings it down to 0.15 seconds, but it seems I need to force MySQL to use that index in place of just content_user_id which I find strange.
Any insight into this issue is greatly appreciated. I can't tell if this is just a complex query that requires this amount of time, or I'm doing something wrong. I'm leaning towards it shouldn't be taking that long If you have any questions about the setup, please let me know.
Thanks in advance.