MySQL Forums
Forum List  »  Performance

Re: COUNT(*) very slowly in large InnoDB table
Posted by: Rick James
Date: July 15, 2010 09:09PM

(I don't know if this will answer any of your questions. But maybe it will give you some more insight into why you are seeing what you are seeing.)

Think of an InnoDB table this way. Your CREATE TABLE is effectively 3 tables:

Your indexes were
PRIMARY KEY (`id`),
UNIQUE INDEX `num` (`project`, `num`),
UNIQUE INDEX `project` (`project`, `token`),

The 3 "tables" contain, each BTree-structured:
1. id, plus all the other fields; this takes 2.6GB.
2. project + num + id -- this takes about 100MB
3. project + token + id -- this takes about 225MB
(The 325 you saw is the sum of the estimated 100 and 225; 225 is bigger on the assumption that `token` is somewhat bigger than `num`.)

#2 has a BIGINT and two INTs -- that's (8 bytes + 2*4 + some overhead) * 4.6M rows.

Each of those 3 "tables" has a PRIMARY KEY (id; project+num; project+token) and can very quickly look up one row by that PK. It can also pretty quickly do a range, such as:
WHERE id BETWEEN 11111 AND 11222
WHERE project = 987 AND token LIKE 'x%'

When you look up something using project and num (using "table" 2), it uses that secondary index to find a list of ids pointing to the data. Then it looks up each data row (in "table" 1) by drilling down the BTree with `id`.

Drilling down a BTree...
Rule of thumb: Each layer of a BTree expands by about 100-fold.
So, your 4.6M rows will take about 4 layers of 'tree'. So, 4 probes and you are sitting at a given row out of the 4.6 million.

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.