Better index stategy for many fields with few values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

I want to optimize something like this.

- My items table:
code int              -- can take one of 100 values
property varchar(250) -- can take one of 5000 values
param01 char(10)      -- can take one of 10 values
param02 char(10)      -- can take one of 10 values
...
[ 20 similar columns }
...
parama20 char(10)     -- can take one of 10 values

- The kind of query I want to optimize:
select * from items
where code betwwen 5 and 22
and param01 = 'P'
and param02 = 'G'
...
[ all the 20 paramXX columns are used in the query}
...
and param20 = 'C';


How can I optimize this kind of query?

I was thinking about using a multicolumns index, but I have read that we should limit multicolumns indice to at most 2 or 3 columns.

If that's true then 22 columns for a multicolumn incdex seems way too much. Or maybe it is workable as every column uses only a very limited set of values?

I was also thinking about about using a functional index.

What do you think would be the best solution in such a case?

Thanks.

Oscar


Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux