加快ORDER BY日期 [英] Speeding up ORDER BY date

问题描述

releases.date现在为DATE类型

releases.date is now DATE type

我正在运行以下查询，该查询大约需要5秒钟才能运行.由于这是一个网络应用程序，因此实在太慢了，无法使用.

I am running the following query which takes around 5 seconds to run. As this is a web app this is far too slow to be usable.

SELECT releases.* ,COUNT(charts_extended.release_id) as num 
        FROM releases_all releases force index (date)           
        JOIN recommendations
        ON releases.id=recommendations.release_id 
        JOIN charts_extended 
        ON charts_extended.release_id=releases.id 
        LEFT JOIN charts_extended ce
            ON ce.release_id=releases.id
            AND ce.artist='Si Quick'
        LEFT JOIN dislike
            ON dislike.release_id=releases.id
            AND dislike.user='Si Quick'
            WHERE dislike.release_id IS NULL 
        AND ce.release_id IS NULL 
        AND recommendations.user='Si Quick'
        AND datediff(now(),releases.date) >=0
        GROUP BY releases.id
        ORDER BY releases.date DESC 
        LIMIT 0,41

EXPLAIN返回以下内容:

EXPLAIN returns the following:

1   SIMPLE  releases    ALL NULL    NULL    NULL    NULL    77226   Using where; Using temporary; Using filesort
1   SIMPLE  ce  ref release_id,artist   release_id  4   soundshe.releases.id    4   Using where; Not exists
1   SIMPLE  recommendations ref user,release_id release_id  4   soundshe.releases.id    39  Using where
1   SIMPLE  dislike ref release_id,user user    203 const   105 Using where
1   SIMPLE  charts_extended ref release_id  release_id  4   soundshe.releases.id    4   Using index

Using temporary和Using filesort大大降低了查询速度.如果我删除ORDER BY releases.date DESC命令，则查询将在〜1秒内运行.

Using temporary and Using filesort are slowing down the query considerably. If i remove the ORDER BY releases.date DESC command then the query runs in ~1 second.

releases.date字段中的数据采用YYYY-MM-DD格式，并且是VARCHAR类型.

Data in the releases.date field is in the format YYYY-MM-DD and is a VARCHAR type.

如何加快此ORDER BY的速度?我已经在该字段上设置了索引.

How can I speed up this ORDER BY? I have set an index on the field.

推荐答案

从最简单到最困难的部分，它最物有所值:

Most bang for buck, from easiest to hardest:

1. 仅选择所需的字段. *实际上会增加可观的开销.只需尝试删除该位，看看您能获得多少改进.

1. Choose only the fields you need. * can actually add a considerable overhead. Just try deleting that bit and see how much of an improvement you get.

SELECT COUNT(charts_extended.release_id) as num 

请考虑使用整数字段作为索引，因为日期可以重复.如果日期实际上是日期而不是日期时间，则索引会更糟.从您的解释性陈述来看，我认为这实际上并没有做任何事情.

Consider using an integer field as an index instead, since dates can be duplicated. If the date actually a date and not a datetime, the index is worse. I dont think this is actually doing anything judging from your explain statement.

FROM releases_all releases force index (date) 

确保WHERE子句中的所有内容都具有索引设置.

Ensure everything in your WHERE clause has an index setup.

在此处手动传递日期时间而不是在now()中传递日期时间，以便它可以进行缓存，当前每次都会从头开始整理结果集.您过去一直在寻找东西，所以您可以使用从明天起的datediff进行操作，因为音乐专辑/唱片通常在9月28日(星期二)而不是9月28日(星期二)在发布上午9点.这将完成一次工作，而不是每个参数每次都做.当然，这将在SQL语句中使用参数. 您可以尝试在此期间手动插入日期，然后运行两次查询以了解我的意思:

Pass a datetime in here manually instead of now() so it can cache, this will currently cook up the resultset from scratch each time. You are looking for stuff in the past, so you can probably do this with a datediff of from tomorrow since music albums/records are usually released on Tuesday 28th September, rather than Tuesday 28th September at 9AM. This will do the work once rather than each time per parameter. This will be using a parameter in an SQL statement of course. You can try inserting a date by hand in the meantime and running the query twice to see what I mean:

AND datediff("2017-06-28 00:00:00",releases.date) >=0
GROUP BY releases.id
ORDER BY releases.date DESC 
LIMIT 0,41

这是您的新改进的查询，其中插入了参数，请运行两次以查看是否有任何改进:

Here is your new improved query with parameters injected, run it twice to see if you get any improvement:

SELECT COUNT(charts_extended.release_id) as num 
        FROM releases_all releases         
        JOIN recommendations
        ON releases.id=recommendations.release_id 
        JOIN charts_extended 
        ON charts_extended.release_id=releases.id 
        LEFT JOIN charts_extended ce
            ON ce.release_id=releases.id
            AND ce.artist='Si Quick'
        LEFT JOIN dislike
            ON dislike.release_id=releases.id
            AND dislike.user='Si Quick'
            WHERE dislike.release_id IS NULL 
        AND ce.release_id IS NULL 
        AND recommendations.user='Si Quick'
        AND datediff("2017-06-28 00:00:00",releases.date) >0
        GROUP BY releases.id
        ORDER BY releases.date DESC 
        LIMIT 0,41

这篇关于加快ORDER BY日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！