2
\$\begingroup\$

I'm writing a query to get the number of sessions created each day for the past 90 days. I'm currently using a prepared statement to do this - looks something like this:

for($n = 0; $n < 90; ++$n){
    $days[] = $n;
}

$st = $this->db->prepare("
    SELECT * 
    FROM `sessions` 
    WHERE DATE(sessionStart) = DATE_SUB(CURDATE(), INTERVAL :n DAY)
");

$bind = array(':n' => null);
$data = array();

foreach($days as $day){
    $bind[':n'] = $day;
    $st->execute($bind);
    $data[$day] = $st->rowCount();
}

Would my code perform better using either of these?

  1. This code, which is essentially running 90 separate queries
  2. Running one query to return everything from the sessions table and then sorting the results in PHP
\$\endgroup\$
2
\$\begingroup\$

Aside from @Sumurai8 recommendations, the sql query can be indeed complexified a bit to return all the results in one go:

SELECT DATE_FORMAT(`sessionStart`, '%Y-%m-%d') AS `day`, COUNT(*) AS `sessions`
FROM `sessions` 
WHERE 
    DATE(`sessionStart`) BETWEEN DATE_SUB(CURDATE(), INTERVAL 90 DAY) AND CURDATE()
GROUP BY
    `day`
ORDER BY
    `day`

It only needs to be executed once, and then iterated over the result set using your favorite FETCH_XXX method.

\$\endgroup\$
1
\$\begingroup\$

Undefined behaviour

The documentation on PDOStatement::rowCount (docs) tells us this:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

You should use COUNT(*) instead in the query.

Unnecessary work

You are creating an array, then loop over it. You can just put the code directly in the for-loop and skip the generation of the (useless) array.

The art of binding

You are passing a variable $bind to execute, but a more elegant way of doing it is binding the variable once with PDOStatement::bindParam() (docs), then just update the value of that variable.

Single query or multiple queries

The corrected query is easily transformed into a single query by using GROUP BY. By using ORDER BY you can make sure the rows are returned in the right order.

Theoretically, in this case the large query is likely better in performance, because you give mysql the space to optimize the performance of the entire query. To actually be sure, you'll have to test both versions on your own.. The biggest performance impact you currently have is not that you are doing single queries, or multiple queries, but rather that you selecting information that you do not need.

\$\endgroup\$
  • \$\begingroup\$ but even using COUNT() would result in 90 separate queries to the DB, no? the question here is whether running multiple queries would perform worse than just running ONE query to select all records from a given time period (ie.: last 90 days), then sorting the results using PHP. \$\endgroup\$ – fireinspace Jan 5 '16 at 19:07
  • \$\begingroup\$ I had to save (read: post) this answer before I went home from work. Please keep in mind that while you can define specific points you would like to have a review on, answers on Code Review can be about any aspect of the code. I did not elaborate on the topic of performance of multiple queries/one query, because I did not have enough time to think through what code would be necessary if you did just one query. Even then, it is likely impossible to tell what will work better in your case. \$\endgroup\$ – Sumurai8 Jan 5 '16 at 19:23
  • \$\begingroup\$ did you mean ORDER BY? \$\endgroup\$ – Sebas Jan 5 '16 at 20:46
  • 1
    \$\begingroup\$ Yes, I meant that. I blame sleep. And random unicorns. \$\endgroup\$ – Sumurai8 Jan 5 '16 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.