There are many categories and subcategories on the bulletin board site, links to everything on the main page. Counting the number of ads goes through the SELECT COUNT ( ) + condition. It turns out that when you open the main page, dozens of SELECT COUNT ( ) are executed , how to optimize this, make a separate table and take it as a script from time to time or are there any other methods?

  • as an option, you make a trigger for adding and deleting records. and keep the current counter in another table - splash58
  • It is possible to conjure something like a cache and not to access the database every time. - Batanichek
  • one
    If all columns used in the condition are included in one index, then most likely the counting of rows will follow the index, without looking at the data. What can be seen in the execution plan for the query using the "index" in the Extra - Mike field

1 answer 1

count for transactional DBMS (innodb in particular) is generally creepy. You need to execute the query, find all matching rows of the table, raise them all into memory, check whether this row is visible in this transaction. This is the MVCC, the line can already be deleted in another transaction, and with update it is possible that several different versions of one line exist at the same time - and you need to find out which of them is visible in this transaction.

Nontransactional myisam in translation means “I don’t need this data, they will be lost in case of anything, and figs with them”, therefore I’m not even talking about it.

Now the question is what to do with it. The answer depends on what and how much should be received. I did not quite understand why do you have dozens of different count when you open the page instead of one aggregated. So maybe some kind of specificity.

  • In general, the task “how many ads we have in a category” is convenient to solve with a bundle of triggers. Started in a separate plate, or more often in the same category table, the field for the number of ads in the category. The trigger on insert in the table of declarations makes +1, the trigger on delete is -1, the trigger on update checks whether the category number has changed. If changed, respectively, makes -1 old category and +1 new. Counters "show up to the specified date and time" becomes so inconvenient
  • If the strict relevance of the counter is not required (for such a task it is not usually needed, well, there will be a full minute of announcements 232, and not 230, okay), then the counters can also be thrown into a separate table and considered as a scheduler. By the way, mysql has a regular event scheduler for which this task can be hanged. The logic of counters in one place, and not in a whole bundle of triggers, is quite normal to consider announcements with a date limit.

Often used approaches all. You can immediately do + -1 in the code when dealing with ads, but these are the same triggers obtained.