如何提高SQL的抓取速度?

I am not expert at MySQL indexes, but I saw a lot of tutorials, still my page takes 7 seconds to load, using PHP.

我不是MySQL索引的专家，但是我看到了很多教程，我的页面仍然需要7秒才能加载，使用PHP。

I have around 50k rows, and 30 columns in MySQL tables.

我有大约50k行，在MySQL表中有30列。

How can I improve MySQL Fetching data speed? Anything that I can improve in this below query?

如何提高MySQL抓取数据的速度?在下面的查询中有什么我可以改进的吗?

SELECT tmdb_movies.movie_title,tmdb_movies.budget,tmdb_movies.original_language,tmdb_movies.original_title

,translations.translations_english_name

,videos.videos_name,videos.videos_key



FROM tmdb_movies 


LEFT JOIN
(
  SELECT 
    translations_tmdb_id
,GROUP_CONCAT(DISTINCT translations.translations_english_name SEPARATOR ', ') AS translations_english_name

  FROM translations
  GROUP BY translations_tmdb_id
) translations ON translations.translations_tmdb_id = tmdb_movies.tmdb_id


LEFT JOIN
(
  SELECT 
    videos_tmdb_id
,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
  FROM videos
  GROUP BY videos_tmdb_id
) videos ON videos.videos_tmdb_id = tmdb_movies.tmdb_id

Where tmdb_movies.tmdb_id= '$tmdb_id'

Here, I use tmdb_id to connect all tables. tmdb_id,translations_tmdb_idand videos_tmdb_id are indexed in MySQL.

这里，我使用tmdb_id连接所有表。tmdb_id、translations_tmdb_idand videos_tmdb_id在MySQL中被索引。

Here is a sample of my MySQL table structure:

下面是我的MySQL表结构示例:

tmdb_movies table:

tmdb_movies表:

tmdb_id      movie_title
1            Logan
2            Iron Man
3            Superman

translations table

翻译表

translations_tmdb_id      translations_english_name 
1                         English
1                         Hindi
1                         French 
2                         English
2                         Spanish
2                         Hindi

videos table

视频表

videos_tmdb_id          videos_name
1                       Official Trailer
1                       Trailer 2 
2                       Trailer 1
2                       Trailer 2 HD
3                       Superman Trailer 1
3                       Superman Trailer 2

2 个解决方案

#1

Why GROUP_CONCAT(DISTINCT ...)? There are no duplicates.
为什么GROUP_CONCAT(不同…)?没有重复的。
You are only selecting data for one movie. But your derived tables read and aggregate all movies. A great optimizer will look through this and only aggregate records for that one movie. If this were Oracle I would expect exactly this. But with MySQL? I wouldn't rely on this. So add a where clause to your subqueries.
你只是在为一部电影选择数据。但是你的派生表会读取并聚合所有的电影。一个优秀的优化器将检查这个，并且只聚合该电影的记录。如果这是甲骨文的话，我完全可以预料到。但与MySQL呢?我不会相信的。所以在子查询中添加where子句。
You shouldn't select video names and keys without an order by clause. Order them so both have the same order, i.e. the first name matches the first key etc.
没有order by子句，不应该选择视频名和键。对它们进行排序，使它们具有相同的顺序，例如，名称与第一个键匹配等。

Here is your query re-written:

以下是您重新编写的查询:

SELECT 
  m.movie_title,
  m.budget,
  m.original_language,
  m.original_title,
  t.translations_english_names,
  v.videos_names,
  v.videos_keys
FROM tmdb_movies m
CROSS JOIN
(
  SELECT 
    GROUP_CONCAT(translations_english_name SEPARATOR ', ') AS translations_english_names
  FROM translations
  WHERE translations_tmdb_id = @tmdb_id
) t
CROSS JOIN
(
  SELECT 
    GROUP_CONCAT(videos_name ORDER BY videos_name) as videos_names,
    GROUP_CONCAT(videos_key ORDER BY videos_name) as videos_keys
  FROM videos
  WHERE videos_tmdb_id = @tmdb_id
) v
WHERE m.tmdb_id = @tmdb_id;

And these are the indexes you should use:

这些是你应该使用的索引:

create index idxm on tmdb_movies(tmdb_id); -- if tmdb_id is PK, you have this already
create index idxt on translations(translations_tmdb_id, translations_english_name);
create index idxv on videos(videos_tmdb_id, videos_name, videos_key);

#2

Your main SELECT statement is probably fine; you're filtering on the autoincrementing primary key.

您的主选择语句可能没问题;您正在过滤自动递增的主键。

It's the two subqueries where the opportunities may lie for improving performance.

这是两个子查询，其中的机会可能是为了提高性能。

The first one:

第一个:

  SELECT 
    translations_tmdb_id, 
    GROUP_CONCAT( DISTINCT translations.translations_english_name 
                  SEPARATOR ', ') AS translations_english_name    
  FROM translations
  GROUP BY translations_tmdb_id

A compound index (sometimes called a composite index or multi-column index) on translations_tmdb_id, translations_english_name has the potential to help this subquery a lot. Why? It can help with both the GROUP BY and the DISTINCT parts of the query.

在translations_tmdb_id上的复合索引(有时称为复合索引或多列索引)，translations_english_name具有很大的潜力来帮助这个子查询。它可以帮助组BY和查询的不同部分。

The second one:

第二个:

  SELECT 
    videos_tmdb_id
,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
  FROM videos
  GROUP BY videos_tmdb_id

The same priniciple applies, but the two distinct DISTINCT clauses will slow things down a little. Try a compound index on videos_tmdb_id, videos_name, videos_key. It should help.

同样的原则也适用，但是两个截然不同的子句会使事情慢下来一点。在videos_tmdb_id、videos_name、videos_key上尝试复合索引。它应该帮助。

#1

Why GROUP_CONCAT(DISTINCT ...)? There are no duplicates.
为什么GROUP_CONCAT(不同…)?没有重复的。
You are only selecting data for one movie. But your derived tables read and aggregate all movies. A great optimizer will look through this and only aggregate records for that one movie. If this were Oracle I would expect exactly this. But with MySQL? I wouldn't rely on this. So add a where clause to your subqueries.
你只是在为一部电影选择数据。但是你的派生表会读取并聚合所有的电影。一个优秀的优化器将检查这个，并且只聚合该电影的记录。如果这是甲骨文的话，我完全可以预料到。但与MySQL呢?我不会相信的。所以在子查询中添加where子句。
You shouldn't select video names and keys without an order by clause. Order them so both have the same order, i.e. the first name matches the first key etc.
没有order by子句，不应该选择视频名和键。对它们进行排序，使它们具有相同的顺序，例如，名称与第一个键匹配等。