MySQL Forums
Forum List  »  Performance

Re: small DB, big problems
Posted by: Rick James
Date: September 11, 2009 09:53AM

You are using InnoDB. Most of the caching goes through innodb_buffer_pool_size, which is only 8M. Can you spare some more RAM? On a 2GB machine running just MySQL, set that to at least 1000M, but not so much that it would "swap".

UNIQUE KEY `TagNameMainUIndex_1` USING BTREE (`TagName`,`TagMain`),
->
UNIQUE KEY `TagNameMainUIndex_1` USING BTREE (`TagMain`,`TagName`),
would make better use of
AND TagMain = 'n'
(Caution: this change could adversely impact other queries)

As your dataset grows, the ORDER BY RAND() will be more and more a burden. Fish around for other ways to a random row. It's a moderately common thing to do.

Also, by scanning the entire table (because of ORDER BY RAND()), queries may be blocking each other. Look into the levels of transaction locking; the rand queries probably could get by with a less-strict mode. (Don't know if you can really avoid the locking issues here.) The "rows" column of the EXPLAIN is indicating that it is effectively searching the entire table.

Currently the number of rows is so small that RAND should not be a big contributor.

If you happen to have consecutive ids (no gaps), then computing a random number between min(id) and max(id) would give you a pointer to the one random row to use. (Gets messier where you want 2 or 3 rows.) If you don't have such an id, you might have to generate such (perhaps in a separate table that is periodically rebuilt).

Once you have reformulated to avoid RAND in the current way, we can discuss whether turning subqueries into JOINs might help. (It probably would hurt now.)

Options: ReplyQuote


Subject
Views
Written By
Posted
3754
September 08, 2009 12:57PM
1719
September 09, 2009 08:26PM
1656
September 10, 2009 10:39AM
1705
September 10, 2009 07:15PM
1781
September 11, 2009 08:52AM
Re: small DB, big problems
1689
September 11, 2009 09:53AM
1757
September 11, 2009 10:16AM
1929
September 11, 2009 06:20PM


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.