Range Query Performance / Index

MySQL & MariaDB Other MySQL® Questions
1

Hi all!

I got a performance problem with one of my queries and can’t find the right index to fix it.

The query looks like this:

SELECT * FROM table t
WHERE (t.column1 = 1 OR t.column1 = 2) and t.column2 <= 10 ORDER BY t.column2 DESC LIMIT 0, 50

The table-layout looks like this:

id (primary key)
column1 (integer nullable)
column2 (integer not null)
index idx_myindex btree (column1, column2)
table type innodb

Right now the table has about 4 Million Entries (Rows).
The where-Statement “WHERE (t.column1 = 1 OR t.column1 = 2) and t.column2 <= 10” catches about 384.000 rows from the table. Of course not with the limitation of 0, 50.

The explain output which shows a little why this query is that slow is the following:

1, ‘SIMPLE’, ‘t’, ‘range’, ‘idx_myindex’, ‘idx_myindex’, ‘9’, ‘’, 384652, ‘Using where; Using filesort’

On my machine the query takes about: 192 seconds.

How can I set the index right to speed up the query?

Bogi

2

Only if the where clauses on column1 is not very restrictive (say less than 90% of the rows are filtered), a single index on column2 would work.

The only way to speed up this query if the where clause is restrictive, is to add another column that takes value yes if column1 = 1 OR column1 = 2. Then adding an index on (extra column, column2) will speed up your query tremendously. Of course this will only work if only values 1 and 2 are special for column1.

To see how much you will gain, see how fast this query is:
SELECT * FROM table t
WHERE t.column1 = 1 and t.column2 <= 10 ORDER BY t.column2 DESC LIMIT 0, 50