We have a table with over a million rows in it. We're creeping up on 1 gig of storage space. This table is duplicated between two mysql servers. One is in our dev server and running 5.0 and the other is on our live server and is running 5.1. (I know difference between live and dev environments... booo!) The tables are perfectly identical in storage engine, fields and indexes between the two servers.
If we run a simple query to pull data for a specific user for the last two weeks on the live server (mysql 5.1) despite the fact that both the date of the entry and the user are indexed as two separate indexes, the live server takes advantage of neither index and the query takes 3 seconds.
If we run the same query on dev (mysql 5.0) we take advantage of the date index and it pulls the data in a fraction of a second. It still has to chug a bit because there's no cross field index and in all honesty if it's taking a fraction of a second to get the data out of a table with more than a million rows we probably don't need one.
Are there known indexing issues between 5.0 and 5.1? Or is this maybe a case where the upgrade screwed up some indexes and we're not taking advantage of them like we should? I can take advantage of the indexes on the live server for other queries so it may not be an index rebuild issue but I don't know.