CODEWITHSUNDEEP
mysqldatabase-performance
RoyHB
RoyHB

Reputation: 53

slow mysql query that has to execute hundreds of thousand of times per hour

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.

Upvotes: 2

Views: 883

Answers (1)

juergen d
juergen d

Reputation: 204784

select * from tableName
where userName = 'username'
order by datetime_sent desc
limit 1

Upvotes: 2

Related Questions