Search code examples
mysqlsqltimestampcounterminimum

MySQL: Count items by categories

I've created a table that holds items according to categories:

+------------+---------------------+------+-----+-------------------+----------------+
| Field      | Type                | Null | Key | Default           | Extra          |
+------------+---------------------+------+-----+-------------------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
| name       | varchar(30)         | YES  |     | NULL              |                |
| category   | varchar(30)         | YES  | MUL | NULL              |                |
| timestamp  | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| data       | mediumblob          | YES  |     | NULL              |                |
+------------+---------------------+------+-----+-------------------+----------------+

Old data is deleted using a sliding window technique, meaning that only the last N items in each category are kept in the table.

How can I keep track the total number of the items per category, and the timestamp of the first item in the category?

Edit - COUNT and MIN on the original table won't work, because this is a Sliding Window data structure meaning that the first items have already been deleted.

Solution

  • Clearly you need to keep a separate table when you delete the records. Your table should summarize the categories and include the fields:

    • Category first start time
    • Total number of items in the category

    and so on.

    When you go to delete, you need to update this table. In general, I prefer to use stored procedures to handle database maintenance, so this code could be added to the stored procedure. Others prefer triggers, so you could have a delete trigger that does the same thing.