Mysql statistics collection

Question

What would be the easiest way to count new records inserted into a database? Can I include a count request with a download request?

Or do you need something more complicated, for example, recording the last existing record and counting everything that was added after it?

edit:

I have a cron job that uses LOAD DATA INFILE in a script that is passed directly to mysql. This data is used with the php web application. As part of the php web application, I need to generate weekly reports, including how many records were inserted last week.

I cannot fix mysql or radically change the database schema / structure, but I can add new tables or fields. I would prefer not to read the entries from the csv file and save this result in a text file or something like that. Instead, I would rather do everything from PHP with queries.

Answer 1

Assuming you are using Mysql 5 or higher, you can create a trigger that will fire when pasted into a specific table. Note that the "insert" trigger also fires with the "LOAD" command.

, . 1 / . .

MySQL , . http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

, , "" . select() . , , .

MySQL . http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Answer 2

, , , . , 10 ..? - ID?

, , (, datetime), , , , ...

select count(*) from table where currentTime > 'time-you-consider-to-be-new'

, , . , 10000, . 10000 .

Answer 3

, :

 show global status like "Com_%";

. , , , ( , ).

, Munin MySQL.

Answer 4

, API- mysql c "mysql_affected_rows", , . . , , , where.

, , , , .

sobbayi, , , , ( ) .

UPDATE: , , : :

create table row_counts (ts timestamp not null, row_count integer not null);

script :

insert into row_counts (ts,row_count) select now(),count(0) from YOUR_TABLE;
load file inline......
insert into row_counts (ts,row_count) select now(),count(0) from YOUR_TABLE;

row_counts .

Answer 5

? , , . sqlscript, bash script ( grep - ) .

Answer 6

, . , , , ? , - , . csv, csv csv .

Answer 7

, , Nagios ? ( , serferfault.com, .)

Answer 8

, script, . Cron, // .. COUNT . , . , .

Answer 9

, MySQL: http://code.google.com/p/google-mysql-tools/wiki/UserTableMonitoring.

, Google MySQL ( mysql) http://google-mysql-tools.googlecode.com/svn/trunk/mysql-patches/all.v4-mysql-5.0.37.patch.gz. http://code.google.com/p/google-mysql-tools/wiki/Mysql5Patches.

, , , .

Answer 10

, , ?

.

, mysql , 5.0: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

,

Matt

Answer 11

, : , , . . , ( , ).

, Lima , MySQL.

. 655 " MySQL" (2- ) . 158 "SQL " .

Answer 12

"" ? ?

,

select count(*) from yourtable

... , .

, , .. .

?

Answer 13

show global status like 'Com_insert';

flush status show session status... .

. http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Com_xxx

Answer 14

Since you asked for the simplest way, I suggest you use a trigger to insert. You can use a single column, a table with one row as a counter and update it with a trigger.