Posted by Merennulli on 02/12/07 15:42

On Feb 10, 10:55 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Merennulli (mar...@sdf.lonestar.org) writes:
> > Querying it, it takes about 15-20 seconds to do this:
> > Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory
> >>From View
> > Where Region = 58
> > and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
> > like '%2007X%' ;
>
> I completely agree with Dan that normalising ConcatenatedHistory into
> its own table, would give you better performance. But not know the
> tables or indexes its difficult to say why your attempt failed.

Sorry, it looks like my last message in response to Dan didn't go
through.
The problem was indeed my index. It failed because I had the ID first
and the date second.
Flipping the field order in the index brought my time down to about
5-8 seconds.

My thought had been that the query would use start with the other side
of the join - the view, narrow it down first and tie to the index
values, then find the date out of the remaining small section of the
index. Instead it seems it started with the opposite side of the join
from what I expected. Because of that it was within an average of 10
places of being enforced as completely random from the date field's
perspective.

Thanks for pointing me in the right direction.

[Back to original message]