Howdy all, I've noticed some strange behavior with the way that mysql is choosing indexes with a particular query I'm trying to optimize.
First off, I'm using MySQL 4.0.24 on MAC OSX. I've got a table that I'm searching on based upon a set of preferences. From one query to the next the set of preferences may change (as well as the values of the preferences). However, there are a couple of basic preferences that all queries will have, so I created a composite index on that set of preferenes (with the least selective pref being the left most column in the index and getting more restrictive going to the right). I also have another index on the leftmost column mentioned above because that field is a FK and MySQL wouldn't let me use the comp index for the FK. So, there are times when I actually want the single column index to be used and other times the composite key, based on how broad the preferences are. So far so good. I added another index, to see if I could speed things up even more and the query performance took a nose dive (about a factor of 7 worse). When I ran the explain I noticed that mysql changed the index that it was using, but not to the new index (the third one). Adding a fourth index made mysql select the orginal index and performance was restored. My question is, why is mysql choosing differet indexes based on the presence of these new indices (that it chooses not to use in place to the old indices)? I got things back on track by just adding and removing indices until things were working the way that I wanted them to, but it seems really strange. Thanks, Tripp __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
