Please explain why this query is slow.
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 WHEREImage_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.