Entry

Which is faster, a massive OR statement or using IN?

Jan 23rd, 2000 17:28
Nathan Wallace, Boaz Yahav, jim kraai

Is there a smart and more effective way to do :

    select * from Table 
    where (category1='XXXXXXX' or category2='XXXXXXX' or
           category3='XXXXXXX' or category4='XXXXXXX' or 
           category5='XXXXXXX')
    and   (category1='YYYYYYY' or category2='YYYYYYY' or 
           category3='YYYYYYY' or category4='YYYYYYY' or 
           category5='YYYYYYY')

I might be wrong, but this will be faster only if none of the categories
are indexed:

    select * from Table
    where 'XXXXXXX' in 
             (category1,category2,category3,category4,category5)
    and   'YYYYYYY' in 
             (category1,category2,category3,category4,category5);

It's a different way of thinking about it, but it doesn't use indexes.