I've been tasked with optimizing queries. I have the query below
SELECT
vd.video_id,
vd.video_name,
vd.video_title,
vd.video_description,
vd.video_duration,
vd.logo,
vs.balance_mobile AS video_price,
vs.duration,
vs.discount_rate,
vs.discount_start,
vs.discount_end,
vd.video_trailer,
vd.is_trailer,
vs.service_id,
vs.is_hotnew,
vd.rating,
vs.video_order
FROM
videos AS vd
JOIN video_services AS vs ON vd.video_id = vs.video_id
WHERE
vd.is_trailer = 0
AND (
vd.is_approved = 1
OR vd.is_approved = 2
)
AND (
(vd.video_title LIKE '%%forrest gump%%')
OR vd.video_id IN (
SELECT
video_id
FROM
video_tags
WHERE
tag IN (
SELECT
tag_id
FROM
video_tags_labels
WHERE
label LIKE '%%forrest gump%%'
AND language_id = 'ar'
)
)
)
AND vd.is_movie = 1
ORDER BY
vs.is_hotnew DESC,
vs.video_order ASC
I used eversql to help me optimize it. It suggested that I create the following indexes
ALTER TABLE `video_services` ADD INDEX `video_services_idx_video_id` (`video_id`);
ALTER TABLE `video_tags` ADD INDEX `video_tags_idx_video_id` (`video_id`);
ALTER TABLE `videos` ADD INDEX `videos_idx_is_trailer_is_movie_is_approved` (`is_trailer`,`is_movie`,`is_approved`);
and I created one last video_tags_labels (language_id,tag_id);
With the indexes created, the latest version of MariaDB on centos 7, running in a load test with 3000 users and 100 concurrent, the query execution time is 5 seconds.
EverSQL suggested this version instead
SELECT
vd.video_id,
vd.video_name,
vd.video_title,
vd.video_description,
vd.video_duration,
vd.logo,
vs.balance_mobile AS video_price,
vs.duration,
vs.discount_rate,
vs.discount_start,
vs.discount_end,
vd.video_trailer,
vd.is_trailer,
vs.service_id,
vs.is_hotnew,
vd.rating,
vs.video_order
FROM
videos AS vd
JOIN
video_services AS vs
ON vd.video_id = vs.video_id
WHERE
vd.is_trailer = 0
AND (
vd.is_approved IN (
1, 2
)
)
AND (
(
vd.video_title LIKE '%%forrest gump%%'
)
OR EXISTS (
SELECT
1
FROM
video_tags
WHERE
(
EXISTS (
SELECT
1
FROM
video_tags_labels
WHERE
(
video_tags_labels.label LIKE '%%forrest gump%%'
AND video_tags_labels.language_id = 'ar'
)
AND (
video_tags.tag = video_tags_labels.tag_id
)
)
)
AND (
vd.video_id = video_tags.video_id
)
)
)
AND vd.is_movie = 1
ORDER BY
vs.is_hotnew DESC,
vs.video_order ASC
Its explanation is
Prefer IN Clause Over OR Conditions
Using an IN clause is far more efficient than OR conditions, when comparing a column to more than one optional values. When using an IN clause, the database sorts the list of values and uses a quick binary search
Replace In Subquery With Correlated Exists
In many cases, an EXISTS subquery with a correlated condition will perform better than a non correlated IN subquery.
Avoid Comparing Columns From Different Types
Joining or filtering using columns of different types in the same condition may cause performance degradation. The database will have to perform a cast foreach of these values before performing the comparison. Make sure to alter the column types so that common comparisons will be done between two columns of the same type.
Avoid LIKE Searches With Leading Wildcard
The database will not use an index when using like searches with a leading wildcard (e.g. '%%forrest gump%%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
Mixed Order By Directions Prevents Index Use
The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed \"sort\" column (max_sort - sort) and index it instead.
But according to slow query log, the execution time of both queries is the same, is there any reason, the new version is better than the old one?
