On Thu, Sep 23, 2010 at 10:26 AM, Dmitry Teslenko <dteslenko@gmail.com> wrote:
> Hello!
>
> I have this table:
>
> create table test (
> s1 varchar(255),
> s2 varchar(255),
> i1 integer,
> i2 integer,
>
> ... over 100 other fields
>
> );
>
> table contains over 8 million records
>
> there's these indexes:
>
> create index is1 on test (s1);
> create index is2 on test (s2);
> create index ii1 on test (i1);
> create index ii2 on test (i2);
> create index ii3 on test (i1, i2);
>
> and then i run this query:
>
> select
> *
> from (
> select *
> from test
> where
> is1 = 'aa' or is2 = 'aa'
> )
> where
> is1 = 1
> or (is1 = 1
> and is2 = 1)
> or (is1 = 2
> and is2 = 2)
> or (is1 = 3
> and is2 = 3)
hm, I think you meant to say:
s1 = 'aa' or s2 = 'aa', i1 = 1 ... etc. details are important!
Consider taking the combination of 'correct' pair of i1 and i2 and
building a table with 'values' and joining to that:
select * from test
join
(
values (2,2), (3,3), ...
) q(i1, i2) using(i1,i2)
where s1 = 'aa' or s2 = 'aa' or i1=1
merlin