15

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? :)

WASD42
  • 2,352
  • 6
  • 27
  • 41

4 Answers4

13

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.

R. García
  • 815
  • 9
  • 20
Devart
  • 119,203
  • 23
  • 166
  • 186
5
  • 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.
GMD
  • 698
  • 5
  • 12
joe
  • 51
  • 1
3

There is no way to limit the maximum number of a table rows in MySQL, unless you write a Trigger to do that.

aleroot
  • 71,077
  • 30
  • 176
  • 213
0

I'm just making up an answer off the top of my head. My assumption is that you want something like a 'bucket' where you put in records, and that you want to empty it before it hits a certain record number count.

After an insert statement, run SELECT LAST_INSERT_ID(); which will get you the auto increment of a record id. Yes you still have to run an extra query, but it will be low resource intensive. Once you reach a certain count, truncate the table and reset the auto increment id.

Otherwise you can't have a 'capped' table in mysql, as you would have to have pre-defined actions like (do we not allowe the record, do we truncate the table? etc).

Jakub
  • 20,418
  • 8
  • 65
  • 92