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;