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?
Bogi