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

[Vladan Stefanovic]

The most ran queries are thankfully the simplest ones. Like you said, SELECT with WHERE on an indexed column, followed by an UPDATE if `name` is the column in question.

It is also possible that several (hundred) INSERTs will be done in one request, but I'm using prepared statements for those special cases and performance is not an issue there.

SELECT * FROM `test` ORDER BY `hits` DESC LIMIT (100 | 1000 | 10000)
That's the nasty one. I've removed WHERE because all the NULL `name` rows (if there were any inserted) are now deleted immediately after successful INSERT, instead of every 7 days.
Anyway, when running the query above, it takes about 10 seconds to go through 1M rows and it scales accordingly (~50s for 5M, ~200s for 20M, etc).
Using index hint:
SELECT * FROM `test` USE INDEX(`name`) ORDER BY `hits` LIMIT 100
of course, didn't help much as it cut down query time by only 5%.
Using other unindexed fields for ordering gets pretty much the same response time.