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.