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?