application vs database side filter efficiency

Built-in database functions allow for efficient filtering. This post will demonstrate that a simple date filter on the database side is almost twice as fast as filtering the result set on the application side.

For the test, I wrote a basic PHP script that calculates the difference between a start and stop microtime and pulls a result from a database. I tested MySQLi and PDO functions. The scripts look like:

MySQLi

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

$start = microtime(TRUE);
 
$link = mysqli_connect( ... );
 
if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}
 
$query = "SELECT ...";
 
$result = mysqli_query($link, $query) or die(mysqli_error($link));
 
while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
    echo "\n";
}
 
mysqli_close($link);
 
$end = microtime(TRUE);
 
echo $end - $start;

PDO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

//PDO
$start = microtime(TRUE);
 
$dbh = new PDO('mysql:host=localhost;dbname=db', ..., ...);
 
$stmt = $dbh->query('SELECT ... ');
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "\n";
}
 
$dbh = null;
 
$end = microtime(TRUE);
 
echo $end - $start;

The echo "\n"; is to provide a simple O(1) running time.

The test table has 56,000 rows, is 15 columns wide, and 23 MB size on disk.

Baseline

For the baseline, I queried SELECT * on the table. The results were as follows:

Test 1

For the first test, I applied a date filter on the query to return only the last year’s worth of records (SELECT * ... WHERE `date` > CURDATE() - INTERVAL 1 YEAR):

Test 2

For the second test, we return the entire result set and apply the filter on the application side, using two solutions provided here. It should be noted that the first solution calls a function for each row, while the second solution (in PDO) performs the logic inline which does skew the running time. The principle remains the same.

MySQLi

function checkReg($regdate){
    $current = new DateTime();
    if($regdate > strtotime('-1 year', $current->getTimestamp()) ){
        return true;}else{
        return false;
    }
}
 
...
while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
    if(checkReg($row['date'])) {
        echo "\n";
    }
}

PDO

$current = new DateTime();
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if($result['date'] > strtotime('-1 year', $current->getTimestamp()) ){
        echo "\n";
    }
}

Conclusion

MySQLi

Applying the date filter on the database side resulted in a 37.481% decrease from the baseline. Applying the date filter on the application side resulted in a 87.865% increase from the baseline.

PDO

Applying the date filter on the database side resulted in a 39.407% decrease from the baseline. Applying the date filter on the application side resulted in a 58.712% increase from the baseline.

The result set is minimized on the database side, thus resulting in a faster transfer of the data to the application. It’s obvious that in this case, the application side filter creates unnecessary overhead. When built-in functions are available on the database side, they should be utilized. There are instances where using a filter on the database side may impact the performance of the query, but this demonstration shows that there is a heavier cost associated with getting the data to the application.

MySQLi vs. PDO

Just for comparisons sake, I feel it’s worth noting the differences between two extensions.

Across all three tests, PDO had better performance. Again, the larger difference on the application side filter is most likely exaggerated because the logic was not calling a function in the PDO test. The table used in the test is relatively small, and I would be curious to see how the two extensions performed with larger data sets.