Not the answer you need?
Register and ask your own question!

Performance Complex AND / OR-Query

bogi822bogi822 EntrantCurrent User Role Beginner
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

Comments

  • gmousegmouse Mod Squad Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.