Hi, I'm trying to implement the proper indexing for my DB, and am having some challenges. I was hoping someone could lend me a hand.
If I have 5 fields in the DB that I am indexing (field1, field2, field3, field4, and field5), I know I can create an index: Key Index1( field1, field2, field3, field4, field5) which will work as long as fields 1-5 are listed in that order in the where statement. Additionally, I know I can drop off any fields starting at the end and it will still work (ie: where field1=xxx and field2=xxx and field3=xxx). My question, however is the following. Can I use the same index to search for a row where the value of field1 is insignificant? ie: would just want to search on field2, and field3. I have tried the following query without success: select * where ( field1 is null or field1 is not null) and field2 = xxx and field3=xxx If I try an explain on that query, it shows me that all rows from the table must be examined. I have a table with 5,000,000+ rows, and this kind of select will take over 10mins to return. Obviously, non-acceptable. :) Without having to create multiple indexes to cover all possible scenarios (ie: if searching without field2), is there something I can do to use the index? I have also tried (field1 like '%') with the same result. Any insight would be extremely appreciated. Additionally, if there is a better list to be posting to, please let me know. Thanks! Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
