Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    23

    Unanswered: InnoDB Indexes Mysql 5.0 vs 5.1

    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.

    Any help is greatly appreciated!

  2. #2
    Join Date
    Dec 2007
    Posts
    23
    Also of note is that the dev server is using an old set of data so has about 10 or 15% less rows than the live table.

  3. #3
    Join Date
    Dec 2007
    Posts
    23
    I ran a check table (check table table_name) on both tables (live and dev) they both checked out ok.

  4. #4
    Join Date
    Dec 2007
    Posts
    23
    I used a force key (date) to force it to use the index on the date field. That seemed to fix the problem. Still weird that it used the date key in 5.0 by default but not 5.1.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •