Monday, February 27, 2012

Query to find unused blogs in Wordpress

0 comments

I have been administering mysql database running for Wordpress. The system has been running with  HyperDB implementation where we separated every 1000 blogs in different databases.
For maintenance, I have to find the blogs which have not used for more than 1 year and to achieve this I used the following query:
SELECT
SUBSTR(TABLE_NAME,LOCATE('_',TABLE_NAME,2)+1, LOCATE('_',TABLE_NAME,2)-1) AS blog_id,
max(CREATE_TIME) as CREATE_TIME,
max(update_time) as last_update_time,
datediff(now() ,UPDATE_TIME) as days_passed_since_last_update
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'db0'
AND TABLE_TYPE = 'BASE TABLE'
AND datediff(now() ,UPDATE_TIME) > 365
GROUP BY SUBSTR(TABLE_NAME,LOCATE('_',TABLE_NAME,2)+1, LOCATE('_',TABLE_NAME,2)-1)
Output:

blog_id CREATE_TIME last_update_time days_passed_since_last_update
10 2011-01-26 02:37:54 2011-01-26 02:37:54 397
11 2011-01-26 02:37:56 2011-01-26 02:37:56 397
12 2011-01-26 02:37:57 2011-01-26 02:37:57 397
13 2011-01-26 02:37:58 2011-01-26 02:37:58 397
14 2011-01-26 02:38:00 2011-01-26 02:38:00 397
15 2011-01-26 02:38:01 2011-01-26 02:38:01 397
16 2011-01-26 02:38:03 2011-01-26 02:38:03 397
17 2011-01-26 02:38:04 2011-01-26 02:38:04 397
18 2011-01-26 02:38:05 2011-01-26 02:38:05 397
...

Leave a Reply

Labels