Create SQL table for logs (with rotation)

I needed a very simple SQL table with application log. After sometime I’ve realized that it already has 80 MB of data (sic!). What can you do with logs? Well, answer is simple! Use rotation. Below you can find quick SQL solution for removing old entries.

First, create your table schema. I use very simple solution for that:

| id | level | content | date |

I don’t need anything more advanced…

Now, run periodically (using some sort of cron or whatever) following SQL query on your database:

DELETE FROM log WHERE id IN (select id from (select id FROM log ORDER BY id DESC LIMIT :limit, :remove) x)

:limit – stands for how many log entries should stay in table.
:remove – how many entries to remove at once.

This way you’ll remove n (:remove)old entries every time you run your query. Newest entries will stay intact.

Here you can find full gist example.