Customized Suzuki Intruder in French village
Photo: Copyright © 2014 Eelke Blok

Finding number of Drupal-nodes created per hour in a single query

For a customer I've been running an import of old content into a Drupal installation and I got the feeling that the process seemed to run slower and slower. We say "meten is weten" in Dutch ("measuring is knowing"), so I wondered if it was possible to write a single SQL query to find out how many nodes were created per hour since the start of the import. This is the query I came up with:

SELECT FLOOR((node.changed - 1304604369) / 3600) AS hour, COUNT(nid) FROM node WHERE node.changed >= 1304604369 GROUP BY hour;

The "magic number" is the value of the changed field for the first imported node, so you need to find that first. Note, it is used twice in the query; once to calculate the hour since the start of the import, then to limit the number of nodes returned in the WHERE-clause.

You may want to exchange the changed field to the created field instead, but in my case that field contained the original post date, which was meaningless in this context.

The query produced a neat row of hours since the start of the import and number of nodes imported in that houer, which you can then easily paste into a spreadsheet and create some awesome graphs with. It clearly showed my import had an exponential decline, which proofed my gut feeling. Patience...

Add new comment