Как улучшить скорость выборки из SQL?

Я не эксперт в индексах MySQL, но я видел много учебников, все равно моя страница загружается за 7 секунд, используя PHP.

У меня есть около 50k строк и 30 столбцов в таблицах MySQL.

Как я могу улучшить скорость извлечения данных 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'

Здесь я использую tmdb_id для соединения всех таблиц. tmdb_id,translations_tmdb_idи videos_tmdb_id индексируются в MySQL.

Вот пример моей структуры таблицы MySQL:

tmdb_movies таблица: 

tmdb_id      movie_title
1            Logan
2            Iron Man
3            Superman

translations таблица 

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

videos таблица 

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 3
mysql query-performance aggregate-functions

2 ответа:

  1. Почему GROUP_CONCAT(DISTINCT ...)? Здесь нет дубликатов.
  2. вы выбираете данные только для одного фильма. Но ваши производные таблицы считывают и агрегируют все фильмы. Большой оптимизатор будет просматривать это и только агрегировать записи для этого одного фильма. Если бы это был Оракул, я бы ожидал именно этого. Но с MySQL? Я бы на это не рассчитывал. Поэтому добавьте предложение where к вашим подзапросам.
  3. Вы не должны выбирать имена и ключи видео без предложения order by. Закажите их так, чтобы у обоих был один и тот же порядок, то есть первое имя соответствует первому ключу и т. д.

Вот ваш запрос переписан заново: 

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;

И вот какие индексы вы должны использовать: 

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

Ваш основной оператор SELECT, вероятно, в порядке; вы фильтруете по первичному ключу autoincrementing.

Это два подзапроса, в которых могут быть заложены возможности для повышения производительности.

Первый: 

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

Составной индекс (иногда называемый составным индексом или многоколоночным индексом) on translations_tmdb_id, translations_english_name может очень сильно помочь этому подзапросу. Почему? Он может помочь как с GROUP BY, так и с DISTINCT частями запроса.

Второй один: 

  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

Применяется тот же принцип, но два различных предложения DISTINCT немного замедлят процесс. Попробуйте использовать составной индекс на videos_tmdb_id, videos_name, videos_key. Это должно помочь.

2