Re: Please explain why this query is slow.

Brian Capstick writes: } My 2 cents.... } } In the first query the index that it could possibly use has a cardinality } of two. The optimizer seeing that it is likely going to read half the } table anyway - decides to ignore the index and reads the entire table. } } In query 2, the cardinality is four, so the optimizer can assume that } only 25% of the table might be read and decides that the index is a good } thing. } } If you can 'zap' the statistics, tell your database that each table has a } large cardinality and the optimizer will decide to use the index "all" } the time. } Jack Parker wrote: } > 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. Well here's my tuppence worth ! No debate here. If you have an index purely on the isColor field then this is possibly damaging your response time. This is (at least under C-ISAM) due to the number of B+Tree levels that must be used to maintain an index on a field with a cardinality of two. I assume the same applies to Online (Though I won't be to suprised if I'm corrected) } > } 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' } } > } I would again concur with Jack that the SubmitTime field looks like the best candidate for an index. If the vast majority of queries are made using the isColor field then the best candidate may be a composite on Iscolor and SubmitTime. I often create composite indexes on field1 and field2 when field2 is seldom used as a query field just to prevent the kind of skewed index mentioned above. Does anybody have any metrics for the cardinality level at which an index is worthwhile. Or even a rule of thumb. I would probably not have attempted an index on the category field either but these decisions are often made on gut reactions HTH } > } } > } 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 } > } Regards Steve -- ----------------------------------------------------------------------------- EnsignData Ltd. - Informix and Tetra Accounting systems consultancy +44 1634 577054 Steve Weet steve@weet.demon.co.uk