Playing with Database Queries

Primarily as a front-end developer it is rare that I am given the chance to play with back-end logic and database queries. However, last week, I was tasked with creating a relatively simple cron, a script to be run frequently to complete a certain task. The task at hand was to send content to users if they fulfilled appropriate criteria. While the filtering aspect of the work was fairly mundane the real excitement came from being given the chance to investigate best practices and optimisation techniques for database queries inside of Drupal.

Initially I used Drupal's built in node and user load functions to retrieve the necessary data. This was a very resource heavy approach, loading significantly more information than was required. It was at this point I decided to use direct database queries to retrieve the minimum of data. Following some speed testing I settled on db_query, as opposed to db_select, due to the static nature of the queries being performed. Overall I achieved a roughly 10x speed up in the cron execution for medium sized data sets.