0

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 Run time (s) (MySQLi) Run time (s) (PDO)
1 0.613151073456 0.529386997223
2 0.576480150223 0.501909971237
3 0.606681108475 0.533048152924
4 0.554498195648 0.54080915451
5 0.572927951813 0.563175916672
6 0.564739942551 0.553586006165
7 0.600059986115 0.558481931686
8 0.569341897964 0.632627010345
9 0.58523106575 0.56232714653
10 0.642491817474 0.600336074829
Average 0.5885603189469 0.5575688362121

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 Run time (s) (MySQLi) Run time (s) (PDO)
1 0.392072916031 0.342869997025
2 0.379637956619 0.337062835693
3 0.356666088104 0.337620019913
4 0.355556964874 0.342147111893
5 0.37499499321 0.323812007904
6 0.377862930298 0.341898202896
7 0.353166818619 0.343926906586
8 0.379369974136 0.329170942307
9 0.361945867538 0.336555957794
10 0.348371982574 0.343391895294
Average 0.3679646492003 0.3378455877305

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";
    }
}
Test Run time (s) (MySQLi) Run time (s) (PDO)
1 1.09992003441 0.874527931213
2 1.0929889679 0.888898134232
3 1.09219694138 0.882871866226
4 1.09786915779 0.888072013855
5 1.10262298584 0.877489805222
6 1.09084796906 0.897630929947
7 1.11334300041 0.877336978912
8 1.09619688988 0.889336109161
9 1.10290193558 0.895370006561
10 1.1681060791 0.877754926682
Average 1.105699396135 0.8849288702011

Conclusion

MySQLi
Test Average Time (s) % Difference (from baseline)
Baseline: SELECT * 0.588560319 n/a
Database side filter 0.3679646492003 -37.481 %
Application side filter 1.105699396 87.865 %

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
Test Average Time (s) % Difference (from baseline)
Baseline: SELECT * 0.557568836 n/a
Database side filter 0.337845588 -39.407 %
Application side filter 0.88492887 58.712 %

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.

Test MySQLi Time (s) PDO Time (s) Difference
Baseline 0.588560319 0.557568836 5.266 %
Database side filter 0.3679646492003 0.337845588 8.185 %
Application side filter 1.105699396 0.88492887 19.967 %

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.

Norbert Krupa

Technical Consultant

Leave a Reply

Your email address will not be published. Required fields are marked *