SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    InnoDB versus MyISAM Select Performance

    Well, I was wondering how much of a difference InnoDB and MyISAM actually have when selecting, since I've found so much conflicting data. I found one article that basically stated that MyISAM is a table a noob PHP programmer will use. I'm definitely not an expert, but I disagree with that statement.

    I had two identical tables, Production and Production2- being slightly outdated with 40 thousand less rows. I noticed a whopping storage size difference, so I copied the original table and converted to InnoDB which ended up drastically bigger! I added the same indexes (ID Primary AI, and AssocID Index) to the InnoDB tables. They all have about 2.1MM rows of actual data. 12 columns of int, dates, and varchar. Both InnoDB tables somehow ended up with over to have 50 thousand additional rows which I'm still investigating.

    InnoDB: (First Conversion)
    - 167MiB
    - 3.34 - 5.56 seconds

    InnoDB: (Second Conversion)
    - 260MiB
    - 1.77 seconds average

    MyISAM
    - 151MiB
    - 1.05 seconds average


    All tables will do 0.0003 seconds on cached queries.



    Production is the original table. The only difference with Production2 was it was converted prior to an index being added on a second column, and it had about 40k less rows. It also ended up being drastically smaller in size and slower in speed. The overhead on all tables is nothing. It would be interesting to know what's up. In the end, I got what I was after - MyISAM is definitely faster for selects. If I ever need critical data integrity or have hundreds of simultaneous queries occurring on the same table, then I'll consider InnoDB.


    Update: Looks like InnoDB doesn't keep tracking of row count, so it's only an esimated count. The data looked unchanged, so I suspect that's the reason the row count differed.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by biglittle View Post
    Update: Looks like InnoDB doesn't keep tracking of row count, so it's only an esimated count.
    this is correct

    myisam tables maintain a row count and it's easy to do because the entire table is locked for inserts, updates, and deletes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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