Re: Please explain why this query is slow.

Jack Parker wrote: > > } > } > } 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. > _____________________________________________________________________________ I would build composite indexes one on SubmitTime and isColor and one on SubmitTime and Category. Tom