How many sql queries is too many?

Question

How many SQL queries is too many? i have the following piece of code which is in a loop that loops between 28-31 times depending on the month selected. It's used to change a css class depending on the date in the database. Everytime the $rows variable is called does it re-run the query? Is this code efficient?

$sql = ("SELECT * FROM dates WHERE dates.from<='".$date."' AND dates.to>='".$date."'");
$result = mysql_query($sql);
$rows = mysql_num_rows($result);
if ($rows >= 1)
{
    $row = mysql_fetch_array($result);

    if ($rows == 2)
        $calendar .= '<td class="calendar-day-booked">';
    else
    {
        if ($row['from'] == $date)
            $calendar .= '<td class="calendar-day-from">';
        elseif ($row['to'] == $date)
            $calendar .= '<td class="calendar-day-to">';
        else        
            $calendar .= '<td class="calendar-day-booked">';
    }
}
else
    $calendar.= '<td class="calendar-day">';

I only see one query on the database. Am I missing something? — Mike Brant, Dec 14 '12 at 22:03
@MikeBrant OP says they have that code inside a loop. The loop is not shown. — kittycat, Dec 14 '12 at 22:04
Well we can't very well answer the question then can we. Really the question boils down to can you get all the data in one query vs. needing to use 28-31 queries. Rather then seeing all the code the happens after the query, I would rather see the loop and information about the data in the table. — Mike Brant, Dec 14 '12 at 22:06
It seems that you could easily reduce your code to simply send one query and handle the dates within your php code itself, but then again, there's not enough information to say for sure whether you can take this approach. Also, [**don't use `mysql_*` functions**](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-function-in-php). — FThompson, Dec 14 '12 at 22:07
An example of pretty much the same question phrased differently and accepted as a good question: http://stackoverflow.com/questions/2308747/mysql-how-many-queries-per-page-is-too-many — Matthew Brown aka Lord Matt, Aug 01 '16 at 10:51

ernie · Accepted Answer · 2012-12-14T22:16:17.043

There's no definitive answer for what's too much, as there are so many paramters, e.g. how many users, what kind of hardware your database is on, etc.

If your question is "Will this be too slow for end users?" then you should profile the code, and see how it performs.

To answer your question about if each time $rows is assigned, is there a query, the answer is yes - you'll run a SQL query each time you get $rows.

Subjectively, I'd say that's inefficient. If this were my code, I'd rewrite it so that the SQL gets the whole month (e.g. set the date range accordingly), and then process the returned data in PHP, looping over the data and handling the month all at once. I'd probably loop over the returned data once, building an array that was keyed around the dates, and then implode that all together.

How many sql queries is too many?

1 Answers1