Re: Questions about performance for huge table (100M+ rows)

[Vladan Stefanovic]

Yes, it shows it's using a filesort.
But, after I did some more benchmarks with InnoDB and MyISAM (duplicated tables, 10M rows) to compare them side by side, I got some confusing results.

I've ran 1k queries on InnoDB then 1k on MyISAM, then on InnoDB again, and so on until there were 10k queries ran on each table. All are with SQL_NO_CACHE (omitted for readability) and different row was selected each time (but same set of rows on each table). I've listed the results on per query time basis.

SELECT * FROM `test` WHERE `name` = '...'
InnoDB - average: 19.3ms, peak: 117ms
MyISAM - average: 0.9ms, peak: 6ms


Because of the length of this query, I ran only 30 on each table.

SELECT * FROM `test` ORDER BY `updated` LIMIT 100
InnoDB - average: 96.6s, peak 100.9s
MyISAM - average: 17.1s, peak 18.2s

I know that MyISAM is supposed to be faster when it comes to reading than InnoDB, but that seems like quite a bit of difference.
Another weird thing that I noticed is that with InnoDB the more "down" the table (higher id, e.g. the later it was inserted) the row selected by index is, the longer it takes to fetch it. Whereas with MyISAM it makes no difference. But I didn't test this as a part of a benchmark, just an observation while doing manual testing, so it could be random.

With that aside, I would be more than willing to consider adding indexes to columns more frequently used for sorting, summary tables or anything else you had in mind. Like I said, maximum performance and shortest possible response times are what I'm looking for.

Anyway, thanks for all the help so far, really appreciate it :)