MySQL缓慢加入。任何方式加快 [英] MySQL Slow on join. Any way to speed up

问题描述

我有2张桌子。 1是音乐，2是listenTrack。 listenTrack跟踪每首歌曲的独特剧本。我正在尝试获得本月流行歌曲的结果。我得到的结果，但他们只是花了太长时间。以下是我的表和查询

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（` ）
）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 ）NOT NULL DEFAULT'1'，
`play` 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 
  

这个查询不是很复杂，行数不是太大，我不认为。

有没有办法加速？还是建议一个更好的解决方案？这将是每个月开始的一个cron工作，但我也希望在当天的结果中做。

哦，我在这里运行，超过4分钟运行，但在生产它需要大约45秒

解决方案

我更多的是SQL Server的人，这些概念应该适用。

我添加索引：

1. 在ListenTrack ，添加一个包含url的索引，并且date_created


2. 在音乐上添加一个带有url的索引

这些索引应该可以极大地加快查询速度（我最初把表名混合起来 - 修改为最新的编辑）。

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 rows

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 rows

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.

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.

Oh btw i am running this locally, over 4 min to run, but on prod it takes about 45 secs

解决方案

I'm more of a SQL Server guy but these concepts should apply.

I'd add indexes:

1. On ListenTrack, add an index with url, and date_created
2. On Music, add an index with url

These indexes should speed the query up tremendously (I originally had the table names mixed up - fixed in the latest edit).

这篇关于MySQL缓慢加入。任何方式加快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！