Sunday, February 7, 2010

Cool MySQL Database Queries

1. Select a count of all rows in your database:

SELECT SUM(table_rows) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name';


2. Select count of all rows of each table in your database:

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name';


3. Gets stats on a per hour basis, written out to CSV:

SELECT DATE_FORMAT(date, '%m/%d/%Y %H:00:00') AS date, DATE_FORMAT(date, '%Y%m%d%H') AS hour, count(id) AS requests, max(time), avg(time), count(distinct userName) INTO OUTFILE '/tmp/usage.csv' FIELDS TERMINATED BY ',' FROM statslog GROUP BY hour ORDER BY hour ASC;

1 comment:

youdy3000 said...

Thanks for these, Kurt.

The first two work perfectly. Any ideas why the 3rd query isn't working? I have made sure my own .csv file has the correct global write permissions (on my Linux box).

I tried to troubleshoot using MySQL Query Browser but the error reason wasn't very verbose...