MySQL Forums
Forum List  »  Performance

Re: Questions about performance for huge table (100M+ rows)
Posted by: Vladan Stefanovic
Date: January 05, 2009 09:21PM

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 :)

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.