Performance Complex AND / OR-Query

  • Filter
  • Time
  • Show
Clear All
new posts

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


  • #2
    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.