2

I have a table called videos. It's got more columns than this but it should be enough for this example.

+----+-------------+------------+----------+--------+
| ID | Title       | Date       | Quality  | Length |
+----+-------------+------------+----------+--------|
|  1 | video title | 2001-01-01 | 720      | 50     |
+----+-------------+------------+----------+--------+

I want to be able to sort and filter by almost anything in this table. So I created an index for every sortable column and a compound index for the columns you can filter by. It works great until i want to sort and filter at the same time.

in this query it uses the compound index and it's all good. query takes 0.003 seconds

select * 
from videos
where quality = 720
and length = 50
limit 20

here it uses the date index. works great. query takes 0.003 seconds

select * 
from videos
order by Date desc
limit 20

Here it's using the date index. query takes 1.3 seconds

select * 
from videos
where quality = 720
and length = 50
order by Date desc
limit 20

So, obviously it's because it sorts by date then checks every row until it finds 20 matching rows. Sometimes it will be fast and sometimes slow if it needs to go through thousands and thousands of rows until it finds matching rows.

But how can this be sped up? Is caching the only solution here? Or is there something else I can do?

Edit: If i hint to use the compound index it's still slow. I'm guessing it's because it still needs to be sorted before going through them row by row.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
John
  • 281
  • 1
  • 2
  • 9
  • Does this answer your question? [How to hint the index to use in a MySQL select query?](https://stackoverflow.com/questions/11731822/how-to-hint-the-index-to-use-in-a-mysql-select-query) – NineBerry May 10 '21 at 14:47
  • No. It doesn't matter which index I'm using. – John May 10 '21 at 14:50
  • Have you tried "force index" instead of "use index"? – NineBerry May 10 '21 at 14:54
  • The speed is about the same. 1.2 seconds. The problem is that even when using the compound index i'm getting a lot of results. – John May 10 '21 at 14:58
  • You could change the compound index to include the date column as the third column in the index – NineBerry May 10 '21 at 15:01
  • Oh, would this work for sorting as well? I thought it only worked if I'm filtering like "date = 2021-01-02". I will try it. Thanks. – John May 10 '21 at 15:02
  • It depends on how clever the mysql query optimizer is. I know from other database systems that they are clever enough to use a compound index for both filtering and sorting in one query. – NineBerry May 10 '21 at 15:05
  • 1
    **You need to show us the table and index definitions**, as well as row counts for each of the tables. Maybe your tables are defined poorly. Maybe the indexes aren't created correctly. Maybe you don't have an index on that column you thought you did. Without seeing the table and index definitions, we can't tell. We need row counts because that can affect query planning. If you know how to do an `EXPLAIN` or get an execution plan, put the results in the question as well. – Andy Lester May 10 '21 at 15:07
  • What nineberry suggested worked. thanks again. – John May 10 '21 at 15:15

1 Answers1

3

The query you show needs the following index:

ALTER TABLE videos ADD INDEX (quality, length, date)

The first two columns quality and length can be in either order, but they must both be before the column date.

Keep in mind the way MySQL optimizes sorting: queries against InnoDB tables read rows in index order. If the order it reads the rows happens to be the order you requested in your ORDER BY, then it can simply skip any work it would take to sort it. It returns the rows in the order it read them (i.e. index order).

If you have a compound index on (quality, length) alone, there is an implicit extra column which is the primary key. Since you requested a specific value for both quality and length, the rows will therefore be read in primary key order. MySQL can't guarantee this is the same order as date so it must sort the rows by doing extra work.

But if you include the extra column date in the index definition, this is the order the rows will be read in, and so it can optimize away the sorting work.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks this worked. Nineberry did answer first but it was a comment so i will accept this answer. – John May 10 '21 at 15:14