Alexander Staubo wrote:
On 5/23/07, Andy <[EMAIL PROTECTED]> wrote:
An example would be:
SELECT * FROM table
WHERE name like '%john%' or street like
'%srt%'
Anyway, the query planner always does seq scan on the whole table and
that
takes some time. How can this be optimized or made in another way to be
faster?
There's no algorithm in existence that can "index" arbitrary
substrings the way you think. The only rational way to accomplish this
is to first break the text into substrings using some algorithm (eg.,
words delimited by whitespace and punctuation), and index the
substrings individually.
That seems rather harsh. If I'd put an index on each of these colomns
I'd certainly
expect it to use the indices - and I'm pretty sure that Sybase would.
I'd expect
it to scan the index leaf pages instead of the table itself - they
should be much
more compact and also likely to be hot in cache.
Why *wouldn't* the planner do this?
James
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
