0

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?

1 Answer 1

4
  • Use JOINs, not IN ( SELECT ...)

  • Follow the tips for many:many mappings (video_tags) here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

  • The table is called videos; why is there a column is_video?

  • Consider using FULLTEXT instead of LIKE with wildcards. If it will work, it will be much faster when a title is given. Consider writing client code to decide when FT is viable, then add the MATCH clause when it will.

  • Indexes:

    videows:  INDEX(is_movie, is_trailer, is_approved)
    video_service:  INDEX(is_hotnew DESC, video_order ASC)  -- if using MySQL 8.0 (else useless)
    (possibly others; let's see SHOW CREATE TABLEs)
    
  • OR does not optimize well. Use a UNION:

    ( SELECT ... FROM videos vs JOIN ...
        WHERE MATCH(vd.title) AGAINST("+forrest +gump IN BOOLEAN MODE)
          ... )
    UNION DISTINCT
    ( SELECT ... FROM video_tags_labels vtl JOIN ...
        WHERE MATCH(vtl.title) AGAINST("+forrest +gump IN BOOLEAN MODE)
          ... )
    ORDER BY  vs.is_hotnew DESC, vs.video_order ASC
    

(Show us the UNION and the FT and non-ft queries; then there may be more indexes to add.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.