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):
Code:
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
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.
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.
When I run explain, and do tests, it seems to like the index with the timestamp first and then the content_user_id the best. With about 500,000 rows in the table, it takes about .03 seconds. I guess that's not bad. Does this seem reasonable?
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 0, 100
Instead of LIMIT 0, 100 you've put LIMIT 100, so every record was in the result set of your query except those you wanted to select.
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 0, 100
Instead of LIMIT 0, 100 you've put LIMIT 100, so every record was in the result set of your query except those you wanted to select.
Huh? I don't think so.
LIMIT 0, 100 is the same as LIMIT 100. If you only provide one argument to the LIMIT clause, it returns that many rows starting from the beginning of the result set.
0.03 for selecting top 100 out of 500,000 rows? yes, that's quite reasonable.
Could it be faster if MySQL were smarter? You know it.
MySQL's use of indexes is...well, to be generous..."primitive", when compared to the professional DBs such as SQL Server and Oracle.
OK, thanks for the info. Sounds good.
Quote:
Originally Posted by oracleguy
Huh? I don't think so.
LIMIT 0, 100 is the same as LIMIT 100. If you only provide one argument to the LIMIT clause, it returns that many rows starting from the beginning of the result set.
Yeah, this is correct. The query is correct semantically, it just seems like it's a matter of tweaking the indexes.
Before you post, read our: 
If you have any questions about the setup, please let me know.
