Re: Optimize A Simple Query -- Help

tvarnas@compulink.gr (Apostolos Varnas) wrote: :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 ... :-) You can play with update statistics low,medium,high for different columns and other database settings, but you defintly shouldn't rewrite your sql in this way. It's allmost guaranteed to break as soon as you upgrade to a new version. If you rely on this kind of sql's you will have an impossible situation at that time. Nils.Myklebust@idg.no <- New E-mail adress NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company