Adding forename2 to the index did finally work, but only after deleting other secondary indexes that become redundant.

so the moral of the story is: having redunant indexes can impare performance - not just on creating/updating records, but even selects if the Optimizer chooses the "wrong" one i.e. the one you're not expecting as opposed to the Optimizer being wrong.

Thanks for the help

Gareth


________________________________

From: martin.bowes@ctsu.ox.ac.uk [mailto:martin.bowes@ctsu.ox.ac.uk]
Sent: 30 January 2007 09:28
To: Gareth Williams
Cc: info-ingres@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Indexing again


Hi Gareth

The problem would be the test: forename2 like 'AGENT%')

The query has to go back to the table for that detail. And given the number of SMITH% cases out there I suspect that the optimizer is guessing it may as well join back to the main table where it needs to get nasty. Although the optimizer is capable of using many secondary indicies the numbers may not stack up in this case.

Heres a suggestion: add the forename2 to the original index.

See what happens.

Marty
> Select id, surname, forename1, forename2
> From demographic
> Where surname like 'SMITH%'
> And (forename1 like 'AGENT%' or forename2 like 'AGENT%')
> The table has an index on surname, forename1
> The QEP shows that the index IS being used, but then iotrace shows
> that it's also doing a huge table scan. It can take two minutes to do
> this. The final result only brings back 41 records. Surely the:
> surname like 'SMITH%' will cut down the number of records scanned. So¦
> I created another secondary index - just on forename2. optimizedb'd it
> and sysmod'd the database Run the above query and got the exact same
> QEP. So will the Query optimizer only ever use one secondary index?
> Help my brain¦ I may be missing something obvious here... Gareth
> Williams Head of Systems Management Bro Morgannwg NHS Trust Tel. 01656
> 753603
>
> Cymraeg:-
> Mae'r neges hon yn gyfrinachol.Os nad chi yw'r derbynnydd y bwriedid y
> neges ar ei gyfer, byddwch mor garedig â rhoi gwybod i'r anfonydd yn
> ddioed. Dylid ystyried un rhywd datganiadau neu sylwadau a wneir uchod
> yn rhai personol,ac nid o angen rhaid yn rhai o eiddo Ymddiriedolaeth
> GIG Bro Morgannwg, nac unrhyw ran gyfansoddol ohoni na chorff
> cysylltiedig.
>
> Cofiwch fod yn ymwybodol ei bod yn bosibl y bydd disgwyl i
> Ymddiriedolaeth GIG Bro Morgannwg roi cyhoeddusrwydd i gynnwys unrhyw
> ebost neu o hebiaeth a dderbynnir, yn unol ag amodau'r Ddeddf Rhyddid
> Gwybodaeth 2000. I gael mwy o wybodaeth am Ryddid Gwybodaeth, cofiwch
> gyfeirio at wefan Ymddiriedolaeth GIG Bro Morgannwg ar
> www.bromor-tr.wales.nhs.uk English:- This message is confidential. If
> you are not the intended recipient of the message then please notify
> the sender immediately. Any of the statements or comments made above
> should be regarded as personal and not necessarily those of Bro
> Morgannwg NHS Trust, any constituent part or connected body.
>
> Please be aware that, under the terms of the Freedom of Information
> Act 2000, Bro Morgannwg NHS Trust may be required to make public the
> content of any emails or correspondence received. For further
> information on Freedom of Information, please refer to the Bro
> Morgannwg NHS Trust website at www.bromor-tr.wales.nhs.uk.
>


Cymraeg:-
Mae'r neges hon yn gyfrinachol nad chi yw'r derbynnydd y bwriedid y neges ar ei gyfer, byddwch mor garedig â rhoi gwybod
i'r anfonydd yn ddi-oed. Dylid ystyried un rhywd datganiadau neu sylwadau a wneir uchod yn rhai personol,ac nid o angen rhaid yn rhai o
eiddo Ymddiriedolaeth GIG Bro Morgannwg, nac unrhyw ran gyfansoddol ohoni na chorff cysylltiedig.

Cofiwch fod yn ymwybodol ei bod yn bosibl y bydd disgwyl i Ymddiriedolaeth GIG Bro Morgannwg roi cyhoeddusrwydd i gynnwys unrhyw ebost neu
ohebiaeth a dderbynnir, yn unol ag amodau'r Ddeddf Rhyddid Gwybodaeth 2000. I gael mwy o wybodaeth am Ryddid Gwybodaeth, cofiwch gyfeirio
at wefan Ymddiriedolaeth GIG Bro Morgannwg ar www.bromor-tr.wales.nhs.uk

English:-
This message is confidential. If you are not the intended recipient of the message then please notify the sender immediately.
Any of the statements or comments made above should be regarded as personal and not necessarily those of Bro Morgannwg NHS Trust, any
constituent part or connected body.

Please be aware that, under the terms of the Freedom of Information Act 2000, Bro Morgannwg NHS Trust may be required to make public the
content of any emails or correspondence received. For further information on Freedom of Information, please refer to the Bro Morgannwg NHS
Trust website at www.bromor-tr.wales.nhs.uk.