php vs MySQLi, Which one is faster

Question

I want to execute a query to fetch data from 1000 rows in my database. I have found two methods to do that.

Method 1:

SELECT * FROM user WHERE id=879

and second one which I used to protect myself from SQL Injection was:

Method 2:

<?php
$q="SELECT * FROM user";
$get_res=$dbconn->query($q);
while($set=$get_res->fetch_assoc()) {
 if($set['id']==879)
   {
      //Some task here
   }
}

So Which one is faster. I know about SQL prepared Statement.. But I just want to compare these two method.. And if there will be any security flaw in Method2 then Please explain that one also..

Method 2 is horrifically inefficient. You're already using MySQLi so why not bind the paramter?? Check out the very first example at https://stackoverflow.com/a/60496/2191572 — MonkeyZeus, Aug 10 '18 at 17:22
Method 1 will be much faster. Use a prepared statement to bind the ID. Should be no problem since you're already familiar with prepared statements, — Don't Panic, Aug 10 '18 at 17:22
Of course MySQL is going to be faster at querying... that's kind of the whole purpose behind RDBMs.. — Devon Bessemer, Aug 10 '18 at 17:23

score 3 · Accepted Answer · answered Aug 10 '18 at 17:25

If this is just static id=879 you can simply just execute the query. It's fast, performant, and lets MySQL do the filtering for you.

Method 1:

SELECT * FROM user WHERE id=879

Most performant, and is your choice if 879 is not a variable. Just execute the query, return 1 result.

Method 2:

$pdo = new PDO($dsn, $user, $pass, $opt);
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([ $_POST['user_id'] ]);
$user = $stmt->fetch();

This method uses PDO (alternatively you can use bind_param that's available in the MySQLi package). Return 1 result from the database, and safely bind the variable to the query string for execution. This is likely your best solution, as it lets MySQL do the filtering (Using indexes and the most efficient means to find your result).

Method 3 (DO NOT USE):

<?php
$q="SELECT * FROM user";
$get_res=$dbconn->query($q);
while($set=$get_res->fetch_assoc()) {
 if($set['id']==879)
   {
      //Some task here
   }
}

HORRIBLE! You are returning EVERY result from the database, and causing many many needless loops to grab the data you want. Let the database do the filtering, it's what it was designed for!

score 0 · Answer 2 · answered Aug 10 '18 at 17:23

The first (SQL) method is faster.

Reason: SQL is designed for filtering the rows (using the id) very fast. Thus it first searches through all the indexes and returns a single, matching result.

Despite this, with PHP this does the logically the same, but PHP has to fetch the complete row first, validate the id, and proceed to the next row if it does not match.

score 0 · Answer 3 · answered Aug 10 '18 at 19:59

Both are sql querys, only that in the second one you're using php to make the query instead of doing directly as in the first one.

If it's a webpage what you're building, you need to do it rather with php or jsp, because you can't do it directly in this scenario.

However if you're asking which one is faster, it's the first one with no doubts. However it's use is limited to SQL platform rather than globally with php.

score -1 · Answer 4 · answered Aug 10 '18 at 17:25

-1

I assume in the first method you're still using PHP to execute the query.

If you're taking in user input, then in no way is the second method protecting you from SQL injection. You'll need parameterized queries for that.

Also, method 1 is definitely the way to go - the more closely you can get to the desired result without PHP, the better.

answered Aug 10 '18 at 17:25

Joel Rummel

802
5
18

1

The second method is protecting the OP since they aren't using any variables in the query, but it's a horrible way to do it. – Devon Bessemer Aug 10 '18 at 17:26
1

Albeit inefficient, please explain under what condition would method 2 allow for SQL injection? – MonkeyZeus Aug 10 '18 at 17:26
@MonkeyZeus "If you're taking in user input". I was assuming that perhaps the OP was using pseudocode and that the final product would be dynamic, based off of how he seemed to believe that the second method was protected against injection and that the first one wasn't. – Joel Rummel Aug 10 '18 at 17:28
At best, OP's question is unclear and thoroughly misguided but based on the pure code presented, there is no SQL injection possible with method #2 so providing a conjecture-based answer is really ill-advised. Even if it was user input, I can only imagine that the number `879` would be replaced with a variable and even then it's just a PHP comparison... – MonkeyZeus Aug 10 '18 at 17:44

php vs MySQLi, Which one is faster

4 Answers4