Re: Please explain why this query is slow.

Hi, I think I agree with the gist of what's gone before, but have a slightly different slant to things. If the isColor field can have only 2 values, chances are, the engine will decide to read the whole table for query 1, filtered by SubmitTime. For query 2, it may well decide to use the category field index, then filter by SubmitTime. Because of the count(unique itemKey) the engine probably needs to create a temporary table and both queries will populate this table by reading itemKey from the image_master table. Now it becomes somewhat more data dependant. Query 2 may result in much less sequential disk access, and the resulting temporary table may well be much less sorted than for query 1. Both these aspects could contribute to longer execution times for query 2. A final comment is that I try to make master tables have no duplicates in the primary key. If that were enforced, then the count(unique itemKey) could be replaced by count(*) which would make things much faster. Regards, Andy. > > Informix Experts, > > The table image_master looks like > > itemKey INTEGER, { ...master key for all joins } > SubmitTime DATETIME YEAR TO MINUTE, > < many fields deleted > > isColor CHAR(1), { possible values 'T', 'F' } > < many more fields deleted > > category CHAR(1) { possible values 'A', 'I', 'S', 'F' } > > > 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. > > -- =-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-= Andy Lennard andy@kontron.demon.co.uk =-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=-~-=