Performance Complex AND / OR-Query

Hi all!

I got a performance problem / general question how to get the following query fast. Lets start with the table layout:

id (primary key, integer not null autoinc)
column1 (integer not null default -1)
column2 (integer not null default -1)
column3 (float not null default -1)
column4 (tinyint not null default -1)
column5 (integer not null default -1)
column6 (float not null default -1)
table type innodb

The current row-count ist 3 million.

The query is generated from an web-formular where the user can enter the range for all the columns. here is an example:

… where column1 > 10 and column1 < 10 and column2 > 1000 and column3 > 1000 and column5 > -1

of course the user can enter all the ranges for all columns or no columns. at the end of the statement there ist always an:

order by column5, limit 0, 50

i think adding indexes for all possibles query-types would be horrible. what else can I do?


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