mysql query + performance

Question

is there a performance difference between

$a = mysql_query("SELECT * FROM table WHERE id = 1 AND text = 'test'");

and

$a = mysql_query("SELECT * FROM `table` WHERE `id` = '1' AND 'text' = 'test'");

Answer 1

Since table in a MySQL keyword, your first query will give an error.

Since you are enclosing table in back quotes in the 2nd query, it'll run fine.

If you have used table just as a place holder for the table name, then the answer is: No, there will not be any performance difference between the two queries. They will generate exactly same query plan.

Answer 2

No, backticks do not affect performance. They are used to escape reserved words, but you are free to wrap all your table and column names with backticks if you prefer.

I'm not sure if it is a typo, but your second example is not equivalent to the first one when it comes to the 'text' = 'test' part in the WHERE clause. The first will try to match the 'test' value to the text column, while the second one will always return false since 'text' is not equal to 'test'.

Answer 3

In a real case I can't think that there will be any perceptible difference. There are 8 more characters to parse, but that is about it. Having ID as a string means it has to have a type conversion.

Answer 4

There may be a performance penalty for converting to/from an integer/string, depending on the type of column id.

The backticks don't make a difference.