Re: Optimize A Simple Query -- Help

Chao Y. Din wrote: } } I have a simple select count statement that takes three minutes to } execute, yet I only have about 300,0000 rows. Here is the SQL. } } SELECT COUNT(*) FROM table WHERE key[1,7] = "B02800A" AND col1 = 2010 } AND col2 = 10; } } The "key" field is the primary key with data type char(11). "col1" and } "col2" have a composite index. Values of the key field are evenly } distributed, but col1 and col2 are not. The number of possible value of } col1 and col2 do not exceed 50. Col1 and col2 have indexes just because } there are reports searching on these two columns. } } If I drop the col1 and col2 clauses, the query will utilize index and } hence run fast. But combining the substring and the composite key, the } query is no longer using the "key" field for index search. Here is the } output from SET EXPLAIN ON. } } Estimated Cost: 3 } Estimated # of Rows Returned: 1 } } 1) table: INDEX PATH } } Filters: table.key[1,7] = "B02800A" } } (1) Index Keys: col1 col2 } Lower Index Filter: (table.col1 = 2010 AND table.col2 = 10) } } Is it possible to force the query to use index on key without dropping } the composite index? } } Thanks in advance. } } Chao Chao, add another filter condition for the col1 (something like col1 > 0).This will lead the optimizer to use as filters the conditions defined for col1,col2 and as an index the index defined on key. At least it does so on a 5.01 Online ... :-) HTH Tolis +---------------------------------------------------------------------+ | V+K Relational Solutions email: tvarnas@compulink.gr | | Deligiorgi 26 tvarnas@orbit.de | | 546 42 Thessaloniki Voice: (30) 31 820270 | | Greece Fax: (30) 31 865463 | +---------------------------------------------------------------------+