Home / server / Questions / 39516
In Process
Bill Gray
Asked: 2009-07-13 01:01:53 +0800 CST

Creating weekly MySQL reports

  • 772

I have a cron script that runs daily to LOAD DATA INFILE into a MySQL database. I would like to, using PHP as part of a web application, generate a weekly report showing the total number of inserted records from the last 7 days.

I would like to know the best way to go about this. Patching or modifying MySQL is not an option, and changing the way data is inserted is not either. I would prefer to avoid counting the CSV files before inserting, as it would be messy to read this data bac into PHP.

Suggestions?

mysql php

3 Answers

  1. Must it be inside the PHP app? Inside your cron script, you could count the numbers of rows to insert, and add them either to a logfile or into a small log table inside the mysql db (unless that is what you mean with modifiying mysql). I am not sure, but I guess the myql function row_count() will work with INFILE data as well, so counting the numbers would be easy.

    And, maybe the simplest method, if the rows are not modified after inserting, you could add a timestamp column to the database, which gets automatically set to the date of the insert and count() up the numbers for a week.

    • 1

  2. Use the PHP mysql_info() function immediately after you've run your LOAD DATA INFILE query. This will return a string like:

    Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

    See php function ref: mysql_info() Related would be the mysql_affected_rows() function.

    Whatever you do, don't depend upon the row count in SHOW TABLE STATUS. It can be wildly incorrect depending upon your storage engine and is really only useful for the query optimizer to make some better judgments on optimizations. This is documented in SHOW TABLE STATUS (with the specific section quoted here)

    Rows

    The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

    The Rows value is NULL for tables in the INFORMATION_SCHEMA database.

    • 1

  3. If all you are interested is in the number of rows in specific schemas and tables (and assuming that there are no row deletions), then you can use the MySQL command show table status or from the command line use mysqlshow --status dbname. This will list for each table in the schema some extended information including row counts (or you can use like to select specific tables).

    The same data can be retrieved using simple SQL from the INFORMATION_SCHEMA database, maybe like this:

    SELECT Table_name,SUM(Table_rows) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME in ('table1','table2');

    You can create a simple cron job that takes this snapshot once a day and then it should be fairly easy to extract that data in a useful way from PHP. Heck, you can even store it in a database table ;-)

    • 0