MySQL 在加入时慢.有什么办法可以加速 [英] MySQL Slow on join. Any way to speed up
问题描述
我有两张桌子.1 是音乐,2 是 listenTrack.listenTrack 跟踪每首歌曲的独特播放.我正在尝试获得本月流行歌曲的结果.我正在得到我的结果,但他们只是花了太长时间.下面是我的表和查询
I have 2 tables. 1 is music and 2 is listenTrack. listenTrack tracks the unique plays of each song. I am trying to get results for popular songs of the month. I'm getting my results but they are just taking too long. Below is my tables and query
430,000 行
CREATE TABLE `listentrack` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sessionId` varchar(50) NOT NULL,
`url` varchar(50) NOT NULL,
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ip` varchar(150) NOT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=731306 DEFAULT CHARSET=utf8
12500 行
CREATE TABLE `music` (
`music_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`title` varchar(50) DEFAULT NULL,
`artist` varchar(50) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`genre` int(4) DEFAULT NULL,
`file` varchar(255) NOT NULL,
`url` varchar(50) NOT NULL,
`allow_download` int(2) NOT NULL DEFAULT '1',
`plays` bigint(20) NOT NULL,
`downloads` bigint(20) NOT NULL,
`faved` bigint(20) NOT NULL,
`dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`music_id`)
) ENGINE=MyISAM AUTO_INCREMENT=15146 DEFAULT CHARSET=utf8
SELECT COUNT(listenTrack.url) AS total, listenTrack.url
FROM listenTrack
LEFT JOIN music ON music.url = listenTrack.url
WHERE DATEDIFF(DATE(date_created),'2009-08-15') = 0
GROUP BY listenTrack.url
ORDER BY total DESC
LIMIT 0,10
这个查询不是很复杂,行也不是太大,我不认为.
this query isn't very complex and the rows aren't too large, i don't think.
有什么办法可以加快速度吗?或者你能提出更好的解决方案吗?这将是每个月开始时的一项 cron 工作,但我也希望按天完成结果.
Is there any way to speed this up? Or can you suggest a better solution? This is going to be a cron job at the beggining of every month but I would also like to do by the day results as well.
哦顺便说一句,我在本地运行这个,运行时间超过 4 分钟,但在 prod 上大约需要 45 秒
Oh btw i am running this locally, over 4 min to run, but on prod it takes about 45 secs
推荐答案
我更喜欢 SQL Server,但这些概念应该适用.
I'm more of a SQL Server guy but these concepts should apply.
我会添加索引:
- 在 ListenTrack 上,添加一个带有 url 和 date_created 的索引
- 在音乐上,添加带有 url 的索引
这些索引应该会极大地加快查询速度(我最初混淆了表名 - 在最新的编辑中修复了).
These indexes should speed the query up tremendously (I originally had the table names mixed up - fixed in the latest edit).
这篇关于MySQL 在加入时慢.有什么办法可以加速的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!