indexes with multiple likes?

: Greetings, I have been trying to optimize some of our queries, and

: am having limited success. Can some Sybase gurus help out here?

: I am doing a join-less selection on a pretty large table (about

: 2 mil. rows.) It turns out that most of our queries are on only

: a few columns in the table; perfect for indexing. However, the

: query optimizer doesn't seem to like the mixture of the likes that

: we are using.

<snip> (see previous message)

For a concrete example, I've made 13 non-clustered indexes on differing

columns and expanded my original query to something quite obnoxious...

I don't exactly want to make every query as mal-formed as this, but I do

want Sybase to use those indexes... For comparison, the original query

(shown below) takes 430 seconds versus the new 34 seconds... If you can

think of anything I can try to get back to the original readability of

the original and keep some of the speedup of the new query, I would be

most appreciative! No need to be exactly right, just point me in the

right direction. Thanks.

Original:

select * from CNPC1 WHERE ((iof1 like 'N%' or iof1a like 'N%' or iof2 like 'N%'

or iof2a like 'N%' or iof3 like 'N%' or iof3a like 'N%' or iof4 like 'N%' or

iof4a like 'N%') and (loca like 'SLSPMDSS%' or locb like 'SLSPMDSS%' or locc

like 'SLSPMDSS%' or locd like 'SLSPMDSS%' or locz like 'SLSPMDSS%' ))

select * from CNPC1 where iof1 like 'N%' and loca like 'SLSPMDSS%'

union select * from CNPC1 where iof1 like 'N%' and locb like 'SLSPMDSS%'

union select * from CNPC1 where iof1 like 'N%' and locc like 'SLSPMDSS%'

union select * from CNPC1 where iof1 like 'N%' and locd like 'SLSPMDSS%'

union select * from CNPC1 where iof1 like 'N%' and locz like 'SLSPMDSS%'

union select * from CNPC1 where iof2 like 'N%' and loca like 'SLSPMDSS%'

union select * from CNPC1 where iof2 like 'N%' and locb like 'SLSPMDSS%'

union select * from CNPC1 where iof2 like 'N%' and locc like 'SLSPMDSS%'

union select * from CNPC1 where iof2 like 'N%' and locd like 'SLSPMDSS%'

union select * from CNPC1 where iof2 like 'N%' and locz like 'SLSPMDSS%'

union select * from CNPC1 where iof3 like 'N%' and loca like 'SLSPMDSS%'

union select * from CNPC1 where iof3 like 'N%' and locb like 'SLSPMDSS%'

union select * from CNPC1 where iof3 like 'N%' and locc like 'SLSPMDSS%'

union select * from CNPC1 where iof3 like 'N%' and locd like 'SLSPMDSS%'

union select * from CNPC1 where iof3 like 'N%' and locz like 'SLSPMDSS%'

union select * from CNPC1 where iof4 like 'N%' and loca like 'SLSPMDSS%'

union select * from CNPC1 where iof4 like 'N%' and locb like 'SLSPMDSS%'

union select * from CNPC1 where iof4 like 'N%' and locc like 'SLSPMDSS%'

union select * from CNPC1 where iof4 like 'N%' and locd like 'SLSPMDSS%'

union select * from CNPC1 where iof4 like 'N%' and locz like 'SLSPMDSS%'

union select * from CNPC1 where iof1a like 'N%' and loca like 'SLSPMDSS%'

union select * from CNPC1 where iof1a like 'N%' and locb like 'SLSPMDSS%'

union select * from CNPC1 where iof1a like 'N%' and locc like 'SLSPMDSS%'

union select * from CNPC1 where iof1a like 'N%' and locd like 'SLSPMDSS%'

union select * from CNPC1 where iof1a like 'N%' and locz like 'SLSPMDSS%'

union select * from CNPC1 where iof2a like 'N%' and loca like 'SLSPMDSS%'

union select * from CNPC1 where iof2a like 'N%' and locb like 'SLSPMDSS%'

union select * from CNPC1 where iof2a like 'N%' and locc like 'SLSPMDSS%'

union select * from CNPC1 where iof2a like 'N%' and locd like 'SLSPMDSS%'

union select * from CNPC1 where iof2a like 'N%' and locz like 'SLSPMDSS%'

union select * from CNPC1 where iof3a like 'N%' and loca like 'SLSPMDSS%'

union select * from CNPC1 where iof3a like 'N%' and locb like 'SLSPMDSS%'

union select * from CNPC1 where iof3a like 'N%' and locc like 'SLSPMDSS%'

union select * from CNPC1 where iof3a like 'N%' and locd like 'SLSPMDSS%'

union select * from CNPC1 where iof3a like 'N%' and locz like 'SLSPMDSS%'

union select * from CNPC1 where iof4a like 'N%' and loca like 'SLSPMDSS%'

union select * from CNPC1 where iof4a like 'N%' and locb like 'SLSPMDSS%'

union select * from CNPC1 where iof4a like 'N%' and locc like 'SLSPMDSS%'

union select * from CNPC1 where iof4a like 'N%' and locd like 'SLSPMDSS%'

union select * from CNPC1 where iof4a like 'N%' and locz like 'SLSPMDSS%'

(aaarrrrgh!)

-sean.