Indexing beeing ignored

[Jet Black]

Ok I've tried everythign I could find in documentation.

Background:

I have a table of 9 index'd columns, some are varchar, some are int. The indexes can be null and duplicated.

I need to search the table on any number of permutations of the keys such as:

select * from table where key1='asdf' or key2='jkl;' or key3='qwer';

as SOON as I introduce an OR in the expression mysql refuses to use a key lookup and resolves to a table scan. I'm intent on processing atleast 150000 keys, so spending 1.xxx secodns to do a search is unacceptable.

I have found a workaround that I dont like

select * from table where key1='asdf' UNION select * from table where key2='jkl;' UNION select * from table where key3='qwer';

Now since each query uses only 1 key, they are each accomplished using indexing and the unioned query works about 30 times faster. I'm not satisfied with this as dooing from 3 to 9 selects then a duplicate removal to accomplish what can be done with one is not good programming, plus my workaround will slow with the number of keys I search at a time..I.E. if I search all 9 keys at a time, it will noticibly slow down.


I have done the following:
tried FORCE INDEX(key1,key2...)
tried ANALYZE TABLE
upgraded to mysql 4.20 since force index wasnt supported by 3.23
tried all sorts of permutations of the keys, as soon as any OR is introduced they are ignored
checked the oputput from explain select .... to see if my keys were beeing used


I can live with my workaround, but I want a better solution, if I cant get it to use the index's this seems like a nice bug

Cheers

Pierre

[Xnuiem]

Are your indexes on single fields?

[Jet Black]

Yes each field is beeing single indexed, I.E. I have 9 keys, each is indexed...so all in all I have 9 index's.

I cant use multiple column index's since I need to be able to handle any permutation of keys.

I.E. Key1 and key2 or key1 abd key3 or key1 and key4 or key 3 and key 4 blah blah blah.

Of coulrse I could create more and more restrictive muiltiple column keys such as
key1_key2_key3_key4_key5 then key2_key3_key4_key5 then key3_key4_key5 then key4_key5 then key5

taking advantage of mysql's key prefixing. This would work, as long as I allways query using the same order.

Infact its not a bad idea :lol: dammit.....but I cant use it cause when I only have key1 and key3, I have to search key1_*_key3
which I cant do with a multicolumn key