1

We know that single quotes in PHP is faster than double quotes so $foo = 'lorem ipsum'; is faster than $foo = "lorem ipsum";.

But what about in Mysql query? Does single quotes or double quotes affect the execution speed?

Consider the following different syntaxes. Which one is the fastest one? Or is there yet another syntax which is even faster?

mysqli_query($conn, 'SELECT * FROM `mytable` WHERE `full_name` = "' . $full_name. '"');

mysqli_query($conn, "SELECT * FROM `mytable` WHERE `full_name` = '" . $full_name. "'");

mysqli_query($conn, "SELECT * FROM `mytable` WHERE `full_name` = '$full_name'");

mysqli_query($conn, "SELECT * FROM `mytable` WHERE `full_name` = '{$full_name}'");


== Edit ==
I understand that the structure of database and other factors can also affect the execution speed. But let's assume that we already have a well-structured database and exclude other factors.

Ian Y.
  • 2,293
  • 6
  • 39
  • 55
  • 1
    The speed of a query is not how you write it, is on the way you access data. For example, if you query a million users by `user_id(int)` have indexes will faster than you query users by `full_name(varchar(255))`. – Viet Nguyen Sep 21 '17 at 04:50
  • If I was to guess, I would say that the execution speed of a query largely dependent on the data structure and database and not on the PHP syntax. But I could be wrong. – bbrumm Sep 21 '17 at 04:51
  • Thanks guys. I understand your points. But let's assume that we already took care of the database structure and other factors. – Ian Y. Sep 21 '17 at 04:54
  • Short answer is there is no difference. This answer has more info: https://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php – bbrumm Sep 21 '17 at 04:56
  • @bbrumm Thanks. That answer explains the case in pure PHP, while I would like to know the case in Mysql query. Besides, even in pure PHP, single and double quotes do make a difference, don't they? – Ian Y. Sep 21 '17 at 04:59
  • what is stopping you doing your own benchmarking? if you care so much. It maw well depend on mysql engine version –  Sep 21 '17 at 05:01
  • You are not really asking about _MySQL_ but about _PHP_. This has already been answered: https://stackoverflow.com/questions/13620/speed-difference-in-using-inline-strings-vs-concatenation-in-php5 – Octavio Galindo Sep 21 '17 at 05:03
  • @rtfm Doing benchmarking is a way. But a canonical answer (if there is one) is more reliable. Besides, I want to know if there is yet another difference syntax, as mentioned in my question. Please read it. – Ian Y. Sep 21 '17 at 05:04
  • want faster, dont use `SELECT *` any canonical answer could not take all the particular variables of your set up in to consideration. which is why benchmarking your self is the only reliable way to know how it will work on your particular set up. –  Sep 21 '17 at 05:05
  • @OctavioGalindo Thanks. I can see that answer explains the case in PHP. But is the situation the same in Mysql query? – Ian Y. Sep 21 '17 at 05:06
  • Also, whatever speed gain you may get from using one syntax over another does not compare to what you would get from using prepared statements. For a single query may not be worth it, but if you repeat the same query several times, a prepared statement is the way to go, besides, its a good start to avoid sql injection. – Octavio Galindo Sep 21 '17 at 05:06
  • > But is the situation the same in Mysql query? You are building a string to pass mysqli_query(). Php will construct the string THEN call mysql with the result. – Octavio Galindo Sep 21 '17 at 05:07

2 Answers2

2

You are asking about MySQL when MySQL is not involved.

mysqli_query() takes two parameters, a connection to the DB and a query to execute.

The query is a simple string, that you can build however you want.

What you really asking is "what is the fastest way to construct my query string", which is explained in Speed difference in using inline strings vs concatenation in php5? (short answer: concatenation using single quotes).

Another way to construct your query is using prepared statements, which will be slower for a single query (you need to call two functions), but a lot faster when repeating the same query with different parameters.

Regarding the use of quotes within MySQL itself:

SELECT * FROM aTable WHERE a = 'blah';

vs

SELECT * FROM aTable WHERE a = "blah";

It also has been answered in SO previously, take a look at When to use single quotes, double quotes, and backticks in MySQL (short answer regarding single/double, makes no difference, single quotes is the standard)

Octavio Galindo
  • 330
  • 2
  • 9
  • Thanks. So the syntaxes in question does not really involve Mysql. It looks like the first one is the fastest one then. And thanks for letting me know an alternative (prepared statements). – Ian Y. Sep 21 '17 at 05:24
0

SQL Query performance is not dependent on quotation. Double quotes generally is not used in SQL, But quotes can vary from database to database.

SQL Query performance depends on various cases -

Example :

  • Table size
  • Joins
  • Indexing
  • Aggregations etc

Note : But for huge number of rows, Using double quotation (") takes milliseconds differences.

Execute your Query String to phpMyadmin like -

SELECT * FROM YourTable WHERE title ='SOME TITLE';
SELECT * FROM YourTable WHERE title ="SOME TITLE"; #This Query will take some Milliseconds difference for huge number of rows in Different Database
Sumon Sarker
  • 2,707
  • 1
  • 23
  • 36
  • Thanks. Regarding your note, is using double quotation faster or slower? – Ian Y. Sep 21 '17 at 05:08
  • `#This Query will take some Milliseconds difference for huge number of rows` this is simply wrong. mysql is not looking for variables in "" unlike php. both are String Literals to mysql –  Sep 21 '17 at 05:11
  • It depends of huge number of row selection. I updated my Answer – Sumon Sarker Sep 21 '17 at 05:11
  • You can see details here https://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql – Sumon Sarker Sep 21 '17 at 05:14