How to improve download speed from SQL?
I'm not an expert on MySQL indexes, but I've seen many tutorials, but my page takes 7 seconds to load using PHP.
I have about 50k rows and 30 columns in MySQL tables.
How can I improve my MySQL data collection speed? Anything I can improve on this below question?
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 am using tmdb_id
to connect all tables. tmdb_id
, translations_tmdb_id
and are videos_tmdb_id
indexed in MySQL.
Here is an example of my MySQL table structure:
tmdb_movies
table:
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
source to share
- Why
GROUP_CONCAT(DISTINCT ...)
? No duplicates. - You only select data for one movie. But your views read and merge all movies. A great optimizer will look at these and only the aggregate entries for that one movie. If it was Oracle, this is exactly what I would expect. But with MySQL? I wouldn't rely on that. So add a where clause to your subqueries.
- You shouldn't select video names and keys without an order by clause. Order them so that both are in the same order, i.e. the first name matches the first key, etc.
Here's your request rewritten:
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 indices you should be using:
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);
source to share
Your primary operator SELECT
is probably fine; you are filtering the autoincrement primary key.
These are two subqueries where opportunities can lie to improve performance.
The first:
SELECT
translations_tmdb_id,
GROUP_CONCAT( DISTINCT translations.translations_english_name
SEPARATOR ', ') AS translations_english_name
FROM translations
GROUP BY translations_tmdb_id
A composite index (sometimes called a composite index or index multiple columns) on translations_tmdb_id, translations_english_name
may help a lot that a subquery. What for? It can help with both parts of the request GROUP BY
and DISTINCT
.
Second:
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 principle applies, but two different sentences DISTINCT
slow things down a bit. Try a compound index on videos_tmdb_id, videos_name, videos_key
. This should help.
source to share