Please explain why this query is slow.

} } } Informix Experts, Oh well, that knocks me out, but I'll give it a go regardless. } } The table image_master looks like } } itemKey INTEGER, { ...master key for all joins } } SubmitTime DATETIME YEAR TO MINUTE, } isColor CHAR(1), { possible values 'T', 'F' } } category CHAR(1) { possible values 'A', 'I', 'S', 'F' } } Do you have indices on isColor and category? Try removing them. There is no point to an index when there are such a limited set of possible values. You don't include the SET EXPLAIN output portion which explains the search path the optimizer is going to use, but I will bet the the second query gets a cost of 11 because its going to use that nice index on category and then tries to and discovers that its not such a good index after all. While the first one is might think that the datetime field is better - which in fact it probably is. } } QUERY 1: } -------- } SELECT count( unique image_master.itemKey ) FROM IMAGE_MASTER WHERE } Image_Master.isColor = 'T' AND } IMAGE_MASTER.SubmitTime BETWEEN } DATETIME(1996-02-08 00:00) YEAR TO MINUTE AND } DATETIME(1996-02-08 23:59) YEAR TO MINUTE } } Query 2: } -------- } SELECT count( unique image_master.itemKey ) FROM IMAGE_MASTER WHERE } Image_Master.Category = 'S' AND } Image_Master.SubmitTime BETWEEN } DATETIME(1996-02-08 00:00) YEAR TO MINUTE AND } DATETIME(1996-02-08 23:59) YEAR TO MINUTE } } QUERY 1 runs a LOT faster than QUERY 2, even though the SQLs are identical, } and both the fields (iscolor and category) have similar indexes. } } A 'set explain on' results in a cost of 1200+ for QUERY 1 and 11 for QUERY 2. } But the response time on QUERY 1 is MUCH faster than QUERY 2. } } The status of the table is: } } Table Name image_master } Owner informix } Row Size 1278 } Number of Rows 88092 } Number of Columns 33 } Date Created 02/09/1996 } } } Could anyone explain the discrepancy? } } Any and all help appreciated. } } Thanx, } } Raj. } } -- _____________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ "I'm with the IRS, I'm here to help you" _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________