Number of recounts or mysql table caches

Question

I want to show the number of reminders in the queue as statistics on my site. This statistic is the number of rows in my table. What I did in the past is a separate table that caches the number. The cache table has only one row, and one column contains the number of rows in the reminder table. Every time a new reminder is added, I have another request that increments the value by one.

I recently started incorporating the count() function in my project. My question is: is it better to do the work of count() in the table to display the current number of reminders every time someone loads the page (statistics are displayed in the footer), or faster, so that number is already stored in another table so that I just reading from this table? At what point will it be faster to use a cached value rather than a count function?

Answer 1

As is the case with most questions related to optimization, the answer is: well, it depends.

If your table uses the myisam table type, then the number of rows is already cached in the table itself and is counted (*), where this number will not be read.

If you use the innodb table engine and you have a lot of attachments and choose less, then saving the account number will be more expensive than counting the lines. If you have too many inserts, then using a cached number is likely to be faster, since innodb is relatively slow in counting (*) without where.

See the mysql performance blog on account (*) for a more detailed explanation on innodb.

Answer 2

If you are thinking of caching strings, you probably shouldn't, and you probably don't need that. There is a built-in mechanism

SHOW TABLE OF STATUS

Part of the output of this request includes:

The number of rows. Some storage devices, such as MyISAM, store the exact number. For other storage systems such as InnoDB, this value is an approximation and can vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT (*) to get the exact count.

The value of NULL strings for tables in the INFORMATION_SCHEMA database.

This paragraph also quickly answers your question about the effectiveness of SELECT COUNT (*) - on MyISAM tables, it does not depend on the number of rows in the table, since an internal counter is used.

How is innodb different?

Innodb does not store the internal row count in a table because simultaneous transactions can "see" different numbers of rows at the same time. To process the SELECT COUNT (*) FROM t statement, InnoDB scans the index of the table, which takes some time if the index is not completely in the buffer pool. If your table does not change frequently, using MySQL query cache is a good solution. To get a quick score, you must use the counters table that you create yourself, and let your application update it according to the inserts and delete it. If the approximate line score is sufficient, you can use SHOW TABLE STATUS.

Please note that this part of the documentation refers to account caching. But you will notice that if there is an index that completely covers the table, the count (*) query remains fast. Since you naturally have a primary key and that the primary key is likely to be in the buffer, at least in part the performance impact will be negligible.

Note that the story is completely different in another popular open source Postgresql database. There, count (*) slows down in proportion to the size of the table. Fortunately, in mysql this is not so bad.

In conclusion: since the number of cached lines is approximate, you can simply use the status of the show table.

Answer 3

You ask whether it is better to use the count function?, From a technical point of view, this is the best way, but it always depends on how big your data is, causing a count of each page load, it is not a good idea if you process a large amount of data, because that it will affect the rendering of the page, it will make the page load too slow in this case. If you count a small amount of data, then this will be a good and better way.