How to check in real-time if new row was added to MySQL table

Question

We have an automatic car plate reader which records plates of the cars enter to firm. My colleague asked me if we can instantly get the plate number of the car coming. The software uses MySQL and I have only database access. Cannot reach/edit PHP codes.

My offer is to check using a query periodically. For example for 10 seconds. But in this way it is possible to miss the cars coming in 5 seconds. Then decreasing interval increases request/response count which means extra load for the server. I do not want the script to run always. It should run only a new db row added. It shows the plate and exits.

How can I get last recorded row from the db right after inserting? I mean there should be trigger which runs my PHP script after insertion. But I do not know.

What I want is MySQL could run my PHP script after a new record.

possible duplicate of [Querying for a set time?](http://stackoverflow.com/questions/20929045/querying-for-a-set-time) — Markus Malkusch, Jan 09 '14 at 12:12
@PanagiotisGeorgeRaditsas No I do not have access to the script as I mentioned. I can read db only. — zkanoca, Jan 09 '14 at 12:16
You can read db only, but also create a PHP script - but not the PHP insert script? Important detail. — davidkonrad, Jan 09 '14 at 12:27

score 2 · Answer 1 · answered Jan 09 '14 at 12:47

If your table is MyISAM, I would stick to your initial idea. Getting the row count from a MyISAM table is instant. It only takes the reading of one single value as MyISAM maintains the row count at all times.

With InnoDB, this approach can still be acceptable. Assuming car_table.id is primary key, SELECT COUNT(id) FROM car_table only requires an index scan, which is very fast. You can improve on this idea by adding another indexed boolean column to your table:

ALTER car_table ADD COLUMN checked BOOLEAN NOT NULL DEFAULT 0, ADD INDEX (checked);

The default value ensures new cars will be inserted with this flag set to 0 without modifying the inserting statement. Then:

BEGIN TRANSACTION; -- make sure nobody interferes
SELECT COUNT(checked) FROM car_table WHERE checked = FALSE FOR UPDATE; -- this gets you the number of new, unchecked cars
UPDATE car_table SET checked = TRUE WHERE checked = FALSE; -- mark these cars as checked
COMMIT;

This way, you only scan a very small number of index entries at each polling.

A more advanced approach consists in adding newly created cars ID's into a side table, through a trigger. This side table is scanned every now and then, without locking the main table, and without altering its structure. Simply TRUNCATE this side table after each polling.

Finally, there is the option of triggering a UDF, as suggested by Panagiotis, but this seems to be an overkill in most situations.

Nice approach! +1 for "MyISAM maintains the row count at all times" — 0x_Anakin, Jan 09 '14 at 13:02
This also came to my mind: write to an external file from within a trigger (with [`SELECT...INTO OUTFILE`](http://dev.mysql.com/doc/refman/5.6/en/select-into.html)), and monitor this file with something like [`inoticoming`](http://askubuntu.com/questions/43846/how-to-put-a-trigger-on-a-directory/43848#43848). But if you take this route, please do not tell anyone that I suggested this. I will deny. — RandomSeed, Jan 09 '14 at 13:08

score 0 · Answer 2 · edited May 23 '17 at 12:24

Although this is not the greatest of designs and I have not implemented it, there is way to call an external script through sys_exec() UDF using a trigger as mentioned here:

B.5.11: Can triggers call an external application through a UDF?

Yes. For example, a trigger could invoke the sys_exec() UDF.

http://dev.mysql.com/doc/refman/5.1/en/faqs-triggers.html#qandaitem-B-5-1-11

Also have a look on this thread which is similar to your needs.

Invoking a PHP script from a MySQL trigger

How to check in real-time if new row was added to MySQL table

2 Answers2

Linked