How can I set a maximum number of rows in MySQL table?

Question

I need to set a maximum limit of rows in my MySQL table. Documentation tell us that one can use following SQL code to create table:

CREATE TABLE `table_with_limit` 
   `id` int(11) DEFAULT NULL
) ENGINE=InnoDB MAX_ROWS=100000

But MAX_ROWS property is not a hard limit ("store not more then 100 000 rows and delete other") but a hint for database engine that this table will have AT LEAST 100 000 rows.

The only possible way I see to solve the problem is to use BEFORE INSERT trigger which will check the count of rows in table and delete the older rows. But I'm pretty sure that this is a huge overheat :/

Another solution is to clear the table with cron script every N minutes. This is a simplest way, but still it needs another system to watch for.

Anyone knows a better solution? :)

Answer 1

Try to make a restriction on adding a new record to a table. Raise an error when a new record is going to be added.

DELIMITER $$

CREATE TRIGGER trigger1
BEFORE INSERT
ON table1
FOR EACH ROW
BEGIN
  SELECT COUNT(*) INTO @cnt FROM table1;
  IF @cnt >= 25 THEN
    CALL sth(); -- raise an error
  END IF;
END
$$

DELIMITER ;

Note, that COUNT operation may be slow on big InnoDb tables.

On MySQL 5.5 you can use SIGNAL // RESIGNAL statement to raise an error.

Answer 2

• Create a table with 100,000 rows.
• Pre-fill one of the fields with a "time-stamp" in the past.
• Select oldest record, update "time-stamp" when "creating" (updating) record.
• Only use select and update - never use insert or delete.
• Reverse index on "time-stamp" field makes the select/update fast.