Use of indexes (indices?) on Informix DB

I have a table with 5 million+ records on an Informix database.

This isn't the actual table but will show the problem I'm having.

Table: sales

Columns: sale_id, sale_confirmed, vendor_id, purchaser_id

Indexes: idx1(sale_id), idx2(sale_confirmed), idx3(vendor_id), idx4(purchaser_id)

If I do a query like this:

select *  
from sales  
where sale_confirmed IS NULL  
or sale_confirmed = '' 

then the query runs to completion in about 4 or 5 seconds.

If I do a query like this:

select *  
from sales  
where vendor_id = 12345 
or pur开发者_StackOverflow社区chaser_id = 12345

then the query runs to completion in about 4 or 5 seconds.

However, if I run this query (a combination of the 2 previous queries):

select *  
from sales  
where (sale_confirmed IS NULL  
        or sale_confirmed = '' )
and (vendor_id = 12345 
       or purchaser_id = 12345)

then the query ran for 15 mins before I cancelled it.

The database doesn't seem to be smart enough to use the different indexes in tandem i.e. it doesn't seem to be able to use idx2 to find X number of rows AND use idx3 and idx4 within that X number of rows - is that right, I would have thought it would be smart enough to do this?

Is there a way of forcing the database to use idx3 and idx4 when processing the second part of the WHERE clause?

Any other solutions short of creating new indexes?

Thanks.

Try it with a UNION where only two indices have to be chosen from in each part:

select *  
from sales  
where (sale_confirmed IS NULL  
        or sale_confirmed = '' )
and vendor_id = 12345


UNION

select *  
from sales  
where (sale_confirmed IS NULL  
        or sale_confirmed = '' )
and purchaser_id = 12345

And if Informix supports inline views, get the set of rows based on vendor/purchaser and then from that set exclude the unconfirmed sales.

select inlineview.*  from
(
select *  from sales  
where vendor_id = 12345 or purchaser_id = 12345
) as inlineview
where (sale_confirmed IS NULL or sale_confirmed = '')

Finally, I think you might want to drop the low-cardinality index on sale_confirmed.

P.S. I would normally not have a column in my database that allowed the empty string, NULL, and other values. I'd constrain the field probably to a BIT type if you have it, with 1 and 0, with 0 default.

The index on 'sale_confirmed' is unlikely to be useful because the cardinality of 'sale_confirmed' seems to be low (NULL, yes, no?). A better schema design would enforce NOT NULL on 'sale_confirmed' and a CHECK constraint would enforce 'Y' or 'N' and a default could give you 'N' unless you specified otherwise. That would avoid having to do OR operations on 'sale_confirmed', which are messy.

The UNION technique suggested by Tim is likely to be a decent workaround.