MySQL Forums
Forum List  »  Performance

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

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.

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.