My mysql database has a table with several hundred thousand rows.
Each row has (amongst other data) a user name and a time stamp.
I need to retrieve the record with the most recent timestamp for a given user.
The current, brute force query is:
SELECT * FROM tableName WHERE `user_name`="The user name" AND datetime_sent = (SELECT MAX(`datetime_sent`) FROM tableName WHERE `user_name`="The user name");
The table has an index on user_name and on datetime_sent.
How can I best improve this query? I have to run thousands of these queries at a time. Each query seems to be taking between 1 and 4 milliseconds and I suspect there is a much more efficient way to achieve the same result.
解决方案select * from tableName
where userName = 'username'
order by datetime_sent desc
limit 1