2

I have a table which is frequently updated (insert/delete). I also have a script to periodically count how many records are stored in the table. How can I optimize the performance?

  1. Do nothing: Just use the COUNT function.
  2. Create another field to store the number of records: Whenever a new record's added, we increase that field and vice versa.
anhldbk
  • 4,559
  • 5
  • 29
  • 37
  • What is your table engine type ? innodb, myisam, memory ...? – ajreal Jul 21 '12 at 17:22
  • 1
    Is `COUNT` **verified** to be too slow in this case? (There are *no performance numbers posted*, and thus I can only imagine this is a hypothetical question.) The denormalized approach is, well, denormalized .. besides containing duplicated data, an issue with an update could result in *incorrect information*. –  Jul 21 '12 at 17:33

3 Answers3

3

If your database's main function is storing (frequently inserting/updating), switch storage engine to InnoDB, which is faster with INSERT and UPDATE queries, but slower with reading.

Read more here, here or here.

Community
  • 1
  • 1
Nikola K.
  • 7,093
  • 13
  • 31
  • 39
  • is true that innodb has better performance in write, but look for count of a table rows is a READ. – ajreal Jul 21 '12 at 17:28
0

Method #2 is pretty much the standard way of doing it (if your table is incredibly huge and COUNT is giving you performance issues). You could also store the COUNT value in a MEMORY table which would make retrieval exceedingly fast.

Increment/decrement as you see fit.

David Titarenco
  • 32,662
  • 13
  • 66
  • 111
  • Yes, but ... not in this cause. As the objective is to count number of rows in a table without any filtering. – ajreal Jul 21 '12 at 17:27
  • Sure it is. Not only that, but Postgres essentially does exactly this with its special sequence objects - sometimes storing sequences in the table itself (like mySQL does) will be a performance bottleneck as the OP is experiencing. – David Titarenco Jul 21 '12 at 17:30
  • mysql already has a information schema to provide stats of row count for each table ... http://dev.mysql.com/doc/refman/5.5/en/tables-table.html (ps: works for myisam) – ajreal Jul 21 '12 at 17:33
  • I didn't think `COUNT` used the `information_schema` since it always gives the correct number (isn't `information_schema` estimated?) – David Titarenco Jul 21 '12 at 17:39
  • 2
    yup, that's my point, you DUN have to do count ... just seek the row count from information schema IF you are using myisam. – ajreal Jul 21 '12 at 17:40
0

If you need accurate numbers, I would build this into the app the updates the database or use triggers to keep the counts up to date. As others have mentioned, the counts could be kept in a MEMORY table, or a Redis instance if you want performance and persistence. There are counts in the INFORMATION_SCHEMA.TABLES table, but they're not precise for InnoDB (+-20% or more).

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37