Optimising multiple queries

phpSimon

Hi all

I need to get assorted counts from the same database. At the moment I'm doing it the long way; the database is small so it's practically instantaneous, but if anyone has suggestions to make this code more efficient I'd be really interested.

	$date = date('Y-m-d');
	$expired = strtotime ( '-7 day' , strtotime ( $date ) ) ;
	$expired = date ( 'Y-m-d' , $expired );	

$query = "SELECT count(propertyID) AS totalProperties
            FROM properties";
$result = mysql_query($query)or die('Error getting total property count');
if ($r = mysql_fetch_array($result)){
	extract($r);
}

$query = "SELECT count(propertyID) AS expiredProperties
            FROM properties
           WHERE lastConfirmed < '$expired'";
$result = mysql_query($query)or die('Error getting expired property count');
if ($r = mysql_fetch_array($result)){
	extract($r);
}

$query = "SELECT count(propertyID) AS allAvailableProperties
            FROM properties
           WHERE propertyID NOT IN (
           						   SELECT propertyID
           						     FROM bookings
           						    WHERE endDate < '$date'
           							)";
$result = mysql_query($query)or die('Error getting all available properties');
if ($r = mysql_fetch_array($result)){
	extract($r);
}

Thank you

johanafm

The first one is ok since it will simply give the row count for the entire table.

The second one is probably ok, although ranges ( <, >, BETWEEN ) may take time. You could create a script which runs once per day that moves all expired properties from properties to a table called expiredProperties.

The third one should be quicker if you do it using a join instead

SELECT count(*) AS allAvailableProperties
FROM properties p
LEFT JOIN bookings b ON p.propertyID = b.propertyID
WHERE b.propertyID IS NULL
AND b.endData < @date

Oh, and if you want to actually test the effects, create a script which inserts huge amounts of data into your tables. Either just insert random data and insert a million rows or so, or figure out what you expect to have in the future (per day), make it 10 times more and insert data for 1 or 2 years.

After that, issue the different queries and check how long they take. At that point, you'll have a decent idea what to expect in the future. Although real-world data can differ enough that you would get different results. Thus, trying to make your profiling test data as similar as to what you expect might be a good idea.

phpSimon

Thank you, that's great advice.

From a PHP perspective, is there a more efficient way to write this code? Would anyone consider an array of queries, for example, and using a foreach loop - since only the actual query changes for each of the 3 above? There are actually up to 10 queries in this process, I just listed the first 3.

laserlight

phpSimon wrote:
From a PHP perspective, is there a more efficient way to write this code? Would anyone consider an array of queries, for example, and using a foreach loop - since only the actual query changes for each of the 3 above? There are actually up to 10 queries in this process, I just listed the first 3.

That wouldn't make it more efficient, but it may follow the Don't Repeat Yourself principle.

By the way, instead of using the legacy MySQL extension, use the PDO extension or the MySQLi extension. With these, you will be able to use prepared statements, which could improve the efficiency of your code if you are repeating the same statements with only a value changing (e.g., your second query but with $expired replaced by something else).

phpSimon

Thank you.

I don't know enough about PDO or MySQLi to use either yet, but I have started reading up.