Indexing query in MySQL

Question

I'm trying to index this query:

SELECT SQL_NO_CACHE t.title, na.name, na.gender, cn.name
FROM title t JOIN cast_info ci ON t.id = ci.movie_id
JOIN name na ON ci.person_id = na.id
JOIN char_name cn ON ci.person_role_id = cn.id
JOIN movie_info_idx m ON t.id = m.movie_id
WHERE ci.nr_order < 3
AND t.kind_id = 1
AND m.info_type_id = 112
AND m.info < 11
AND ci.role_id IN (1,2)
ORDER BY m.info ASC;

Without index it's 0.77 sec. I've done this index:

CREATE INDEX index3_m ON movie_info_idx(movie_id, info_type_id, info(20));
CREATE INDEX index3_t ON title(kind_id, title(30));
CREATE INDEX index3_ci ON cast_info(movie_id, nr_order, role_id);

Now it's 13.48 sec. So, I'm doing something wrong. My idea was to order movie_info_idx and cast_info to make it easier for the title to access... Here is the database:

Thanks for the help.

EDIT: I add what the query is for: List of films in the top 10 with the name of their main actors / actresses (nr_order <3), if it is an actor or actress and the name of the character they play in the film. Sort the result by the ranking in the top 10 of the movie.

EDIT 2: The explain result:

Actually I mean: run explain select ... rest of your query ... and update your question with result of this statement. It will contain information about how your query will be executed by SQL server — Flying
– Flying, Commented Nov 25, 2017 at 14:24
You have over 500k rows to sort because of title table, it is a reason of slowness. Do you see by yourself any conditions that can be applied to this table to reduce amount of fetched information? — Flying
– Flying, Commented Nov 25, 2017 at 14:42

Rick James · Accepted Answer · 2017-11-25 14:46:18Z

4

Index prefixing is rarely valuable.

That query needs this index on movie_info_idx:

INDEX(info_type_id, info)

in this order, and without prefixing.

For further discussion, please provide SHOW CREATE TABLE (the diagram does not provide datatypes, nor indexes) and EXPLAIN SELECT ... (to see where it is going sour).

Further analysis

First, let me rearrange the JOINs and ANDs to match the order that is probably best:

SELECT  SQL_NO_CACHE t.title, na.name, na.gender, cn.name
    FROM  movie_info_inx m
    JOIN  title t  ON t.id = m.movie_id              -- t: (id)
    JOIN  cast_info ci  ON t.id = ci.movie_id        -- ci: (movie_id)
    JOIN  name na  ON ci.person_id = na.id           -- na: (id)
    JOIN  char_name cn  ON ci.person_role_id = cn.id -- cn: (id)
    WHERE  m.info_type_id = 112
      AND  m.info < 11            -- m: (info_type_id, info)
      AND  t.kind_id = 1          -- t: (kind_id, id)  -- either order
      AND  ci.nr_order < 3
      AND  ci.role_id IN (1,2)    -- ci: (movie_id, then either nr_order or role_id)
    ORDER BY  m.info ASC;         -- included above

I annotated it with likely indexes.

Nothing useful for "covering" if id is the PRIMARY KEY for cn, na. However, this could be useful for ci:

 INDEX(movie_id, nr_order, role_id) -- `movie_id` first

Summary: Add these indexes:

m: (info_type_id, info)
t: (kind_id, id)  -- either order
ci:  (movie_id, nr_order, role_id)  -- `movie_id` first

edited Nov 25, 2017 at 14:46

answered Nov 25, 2017 at 14:31

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jaime Alcántara Arnela Over a year ago

There you have the explain, it's difficult to do the "show create.." as there are four tables there but if you need it I will do it. To be clear: should I use also the other two index?

Rick James Over a year ago

I added 3 indexes that you need for this query. Other indexes may (or may not) be useful for other queries. I assume id is the PRIMARY KEY in any table having id. (If it is not, then I need to make further revisions.)

Jaime Alcántara Arnela Over a year ago

Yeah, id is the PRIMARY KEY in all of them that have it. I'm going to prove it, it's going to take some time, I'll comment when I have it. Thanks

Rick James Over a year ago

@JaimeAlcántaraArnela - One pitfall you may have: Don't blindly use TEXT if a VARCHAR of less than 255 will suffice. There are optimizations (especially in indexes) that cannot be done with TEXT.

Jaime Alcántara Arnela Over a year ago

Thanks for the advice Rick. The query now takes 12.71 secs... What do you think about it?

|

Collectives™ on Stack Overflow

Indexing query in MySQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related