MySQL Forums
Forum List  »  Newbie

Re: Slow queries with very large table
Posted by: Rick James
Date: January 07, 2014 01:04PM

Simply say COUNT(*), not COUNT(col_name), unless you need to avoid counting NULL values.

Change all the BIGINTs you can to INT UNSIGNED -- or smaller.
BIGINT: 8 bytes
INT: 4 bytes
MEDIUMINT: 3 bytes
etc.

Probably the COUNT(*) would use one of the indexes. Each of them is now 16 bytes per row (plus overhead). If you shrink to INT for all fields in one of those indexes, you are down to 8 bytes -- a significant speedup, especially if it needs to hit disk.

InnoDB must walk through all the rows, at least in an index. This is because of transactional integrity. 10 million rows takes time, but probably does not need to take even 3 seconds.

Options: ReplyQuote


Subject
Written By
Posted
Re: Slow queries with very large table
January 07, 2014 01:04PM


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.