Re: Help with indexing

[Chris Nolan]

Hi!

Eric B. wrote:

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.
 

Nope! The way compound indexes work requires that the index be traversed 
in the order it was created and not constraining your search in the 
first column/dimension/variable basically tells the optimiser that it's 
going to need to look at every possible value for the prefix of the index.

Any insight would be extremely appreciated.  Additionally, if there is a
better list to be posting to, please let me know.
Thanks!

Eric



 

Better list? This is the best list on the internet! :-)

Regards,

Chris



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]