Indexing not beeing used

[Pierre Sarazin]

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