Re: How to force use of an index.

Valery Fouques wrote: > > Steve Proctor wrote: > > > > Online version 5 > > > > I've got a very slow select wihch I've analysed using set explain on. > > > > I know it would be quicker if I could force it to use a particular index > > for it's main index path. > > > > Any ideas how to force the optimiser to use an index? > Yes, look in the FAQ ! > Example : > select * from yourtable > where field1=? > and field2=2 > and field2=2 > and field2=2> ... > > The optimizer gives more 'weight' to the column 'field2'. > Can you try and tell me ??? > > Valery (valou@hotmail.com) > Bye Hi Valery and Steve, there's a rule in the optimizer which guarantees that the index of an indexed column will not be used. Put the first indexed column into an expression !!! select * from t1 where f1 + 0 = value and f2 = value; If "f1" is an indexed column, the index will not be used. Is there another index at "f2", this index will now be used. The same rule for datatypes with quotes: select * from t1 where dateval || "" = "12-12-1996" Or select * from address where ad_name || "" = "Weideneder" andad_ci_code = 80804; String concatination with "||" is supported starting with version 5.x. The mentioned rule of the optimizer started with version 4.1. Please !!! Everyone should document such a manipulation of a query. Stefan. stefan@weideneder.de Fax: +49 89 3617015