有什么方法可以改善这种慢查询吗？

Its particular query pops up in the slow query log all the time for me. Any way to improve its efficiency?

它的特定查询一直在慢速查询日志中弹出。有什么方法可以提高效率?

SELECT 
    mov_id, 
    mov_title, 
    GROUP_CONCAT(DISTINCT genres.genre_name) as all_genres, 
    mov_desc, 
    mov_added, 
    mov_thumb, 
    mov_hits, 
    mov_numvotes, 
    mov_totalvote, 
    mov_imdb, 
    mov_release, 
    mov_type 
FROM movies 
LEFT JOIN _genres 
    ON movies.mov_id = _genres.gen_movieid
LEFT JOIN genres
    ON _genres.gen_catid = genres.grenre_id 
WHERE mov_status = 1 AND mov_incomplete = 0 AND mov_type = 1 
GROUP BY mov_id 
ORDER BY mov_added DESC 
LIMIT 0, 20;

My main concern is in regard to the group_concat function, which outputs a comma separated list of genres associated with the particular film, which I put through a for loop and make click-able links.

我主要关注的是group_concat函数,它输出一个逗号分隔的与特定电影相关的类型列表,我通过for循环和点击链接。

3 个解决方案

#1

Do you need the genre names? If you can do with just the genre_id, you can eliminate the second join. (You can fill in the genre name later, in the UI, using a cache).

你需要流派名称吗?如果只使用genre_id,则可以消除第二次连接。 (您可以稍后在UI中使用缓存填写流派名称)。

What indexes do you have?

你有什么指数?

You probably want

你可能想要

create index idx_movies on movies 
  (mov_added, mov_type, mov_status, mov_incomplete)

and most certainly the join index

而且肯定是连接索引

create index ind_genres_movies on _genres
  (gen_mov_id, gen_cat_id)

#2

Can you post the output of EXPLAIN? i.e. put EXPLAIN in front of the SELECT and post the results.

你可以发布EXPLAIN的输出吗?即将EXPLAIN放在SELECT前面并发布结果。

I've had quite a few wins with using SELECT STRAIGHT JOIN and ordering the tables according to their size.

我使用SELECT STRAIGHT JOIN并根据它们的大小订购表,我获得了不少胜利。

STRAIGHT JOIN stops mysql guess which order to join tables and does it in the order specified so if you use your smallest table first you can reduce the amount of rows being joined.

STRAIGHT JOIN停止mysql猜测连接表的顺序,并按指定的顺序执行,因此如果您首先使用最小的表,则可以减少要连接的行数。

I'm assuming you have indexes on mov_id, gen_movieid, gen_catid and grenre_id?

我假设你有关于mov_id,gen_movieid,gen_catid和grenre_id的索引?

#3

The 'using temporary, using filesort' is from the group concat distinct genres.genre_name.

'using temporary,using filesort'来自group concat distinct genres.genre_name。

Trying to get a distinct on a column without an index will cause a temp table to be used. Try adding an index on genre_name column.

尝试在没有索引的列上获得不同的将导致使用临时表。尝试在genre_name列上添加索引。