加速SQL查询，已奏效

debugcn 发表于 Dev

梦鹰

我有这个查询

SELECT distinct(season) as seasons,
        s.name as seriename,
        c.content_digest,
        c.sub_category_id,
        c.source_url,
        c.synopsis,
        c.short_synopsis,
        c.title,
        c.duration,
        c.subtitle_url,
        c.logo_url,
        c.provider_id,
        c.event_date,
        c.start_time,
        c.live,
        c.seriesid as cSerieId,
        c.episode,
        c.season,
        c.content_id FROM content c
        INNER JOIN provider ON provider.id = c.provider_id
        LEFT OUTER JOIN serie s ON s.seriesid = c.seriesid
        WHERE live = '0' AND c.version >= provider.version
        ORDER BY seriename DESC, title
        DESC
        LIMIT 0, 18446744073709551615;

已对其进行索引并进行了一些优化，以快速交付结果（它在0.3秒内交付了44000行）。由于某种未知的原因，这使我不寒而栗，它不再是0.3秒的快速速度，而是2.5秒的快速速度。

这是上面的SQL的解释

1   SIMPLE  provider    index   PRIMARY,provider_id_idx,provider_version_idx    provider_version_idx    5       16  Using index; Using temporary; Using filesort

1   SIMPLE  c   ref c_providerid_idx,c_live_idx,c_version_idx   c_providerid_idx    5   provider.id 3179    Using where

1   SIMPLE  s   ref seriesid_idx    seriesid_idx    97  c.seriesid  1

据我了解，它正在使用其索引，但仍然需要2.5秒。

有什么建议吗？如果需要，我会发布更多信息...

最后结果

SELECT s.name as seriename, 
            c.season as seasons,
            c.content_digest,
            c.sub_category_id,
            c.source_url,
            c.synopsis,
            c.short_synopsis,
            c.title,
            c.duration,
            c.subtitle_url,
            c.logo_url,
            c.provider_id,
            c.event_date,
            c.start_time,
            c.live,
            c.seriesid as cSerieId,
            c.episode,
            c.season,
            c.content_id FROM content c
            INNER JOIN provider ON provider.id = c.provider_id
            LEFT OUTER JOIN serie s ON s.seriesid = c.seriesid
            WHERE live = '0' AND c.version >= provider.version
            GROUP BY (CASE WHEN ( seriename IS NOT NULL )
            THEN seriename ELSE title END) 
            ORDER BY seriename DESC, title DESC
            LIMIT 0, 50

伊姆兰·扎胡尔（Imran Zahoor）

根据服务器的负载和数据集的大小，请求时间可能会有所不同。

我不确定，但是c.version >= provider.version使用join而不是in有时会有所不同WHERE。请参阅下面的完整查询。让我知道EXPLAIN结果。

SELECT distinct(season) as seasons,
    s.name as seriename,
    c.content_digest,
    c.sub_category_id,
    c.source_url,
    c.synopsis,
    c.short_synopsis,
    c.title,
    c.duration,
    c.subtitle_url,
    c.logo_url,
    c.provider_id,
    c.event_date,
    c.start_time,
    c.live,
    c.seriesid as cSerieId,
    c.episode,
    c.season,
    c.content_id FROM content c
    INNER JOIN provider ON provider.id = c.provider_id 
    AND c.version >= provider.version
    LEFT OUTER JOIN serie s ON s.seriesid = c.seriesid
    WHERE live = '0' 
    ORDER BY seriename DESC, title
    DESC
    LIMIT 0, 18446744073709551615;