MySQL Forums :: InnoDB :: count(*) speed in InnoDB

Advanced Search

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:


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 3169 Tito Serenti 02/19/2011 05:13AM
Re: count(*) speed in InnoDB 1403 Rick James 02/20/2011 10:08PM
Re: count(*) speed in InnoDB 1159 Tito Serenti 02/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.