MySQL Forums
Forum List  »  InnoDB

count(*) speed in InnoDB
Posted by: Tito Serenti
Date: February 19, 2011 05:13AM

After an extensive research, I was lead to believe that counting rows in InnoDB tables is a slow operation. But after running some tests, it seems like getting a row count from a table of about 100,000 rows is FASTER than retrieving a single value from a table that stores row count values. Am I missing something? Is the count(*) problem exaggerated, or are my tests incorrect?

Here's what I've done. I need an EXACT row count, and since I was lead to believe that running:

SELECT count(*) FROM table

is slow, I made another table that stores exact row count of all my large tables, and gets updated every time a row is inserted or deleted. So I have a table:

tableName VARCHAR(100) NOT NULL PRIMARY KEY,
value INT NOT NULL

which stores the name of the table and its row count.

But retrieving that single row from the row count table is slower than running a count(*) query on a table with 100,000 rows. I tested it by running a repeated query several thousand times and measuring the results.

So my question is: are my results incorrect because the tests are running on a single machine, or because of some cache, or something else I missed? Or can I just get rid of my strange row counting method and simply use count(*) without worrying about the speed?

Options: ReplyQuote


Subject
Views
Written By
Posted
count(*) speed in InnoDB
3390
February 19, 2011 05:13AM
1444
February 20, 2011 10:08PM
1200
February 26, 2011 08:35AM


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.