慢的mysql查询，每小时必须执行数十万次 [英] slow mysql query that has to execute hundreds of thousand of times per hour

问题描述

我的mysql数据库有一个包含数十万行的表. 每行(除其他数据外)都有一个用户名和一个时间戳.

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.

当前的暴力查询是:

SELECT * FROM tableName WHERE `user_name`="The user name" AND datetime_sent = (SELECT MAX(`datetime_sent`) FROM tableName WHERE `user_name`="The user name");

该表在user_name和datetime_sent上都有一个索引.

The table has an index on user_name and on datetime_sent.

如何最好地改善此查询?我必须一次运行成千上万个这样的查询.每个查询似乎都需要1到4毫秒之间的时间，我怀疑有一种更有效的方法来达到相同的结果.

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

这篇关于慢的mysql查询，每小时必须执行数十万次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！