Reputation: 22674
MYSQL where field in select statement too?
I want to return field name from a database table where id = 4. Which query is easier for MYSQL to process.
SELECT name, id FROM table WHERE id = 4
or
SELECT name FROM table WHERE id = 4
I suppose I'm asking if it's good practice to put the field from the WHERE statement in SELECT as well even if I won't return it in php.
Upvotes: 1
Views: 251
Answers (6)
Reputation: 45589
I suspect the second option, being faster on a big DB (many records > 1M), because less time will be required for fetching the data out of the DB.
Here is a profiling done on a tiny table (1187 records), id is a primary key:
mysql> select name,id from websites where id = 1;
+------+----+
| name | id |
+------+----+
| Shef | 1 |
+------+----+
1 row in set (0.00 sec)
mysql> select name from websites where id = 1;
+------+
| name |
+------+
| Shef |
+------+
1 row in set (0.00 sec)
For the first query:
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000045 |
| checking permissions | 0.000006 |
| Opening tables | 0.000016 |
| System lock | 0.000007 |
| init | 0.000016 |
| optimizing | 0.000006 |
| statistics | 0.000063 |
| preparing | 0.000008 |
| executing | 0.000004 |
| Sending data | 0.000009 |
| end | 0.000003 |
| query end | 0.000002 |
| closing tables | 0.000005 |
| freeing items | 0.000046 |
| logging slow query | 0.000002 |
| cleaning up | 0.000002 |
+----------------------+----------+
For the second query:
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000050 |
| checking permissions | 0.000006 |
| Opening tables | 0.000015 |
| System lock | 0.000008 |
| init | 0.000017 |
| optimizing | 0.000008 |
| statistics | 0.000044 |
| preparing | 0.000008 |
| executing | 0.000002 |
| Sending data | 0.000009 |
| end | 0.000003 |
| query end | 0.000002 |
| closing tables | 0.000005 |
| freeing items | 0.000046 |
| logging slow query | 0.000002 |
| cleaning up | 0.000002 |
+----------------------+----------+
Upvotes: 1
Reputation: 1050
SELECT name, id FROM table WHERE id = 4
would also have the id in the result from mysql and it would make very little difference to the data as it is only a numeral however if you were to do this where you were selecting something else such as SELECT name, id FROM table WHERE name = "ron" would definitely a bad practice as more data is being transferred from the database server as you already know the name "ron" and since it is not unique it could have more than one rows.
mysql> explain select id,name from admin_users where id = 1;
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | admin_users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select name from admin_users where id = 1;
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | admin_users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
compared to
mysql> explain select id,name from admin_users where name = 'ron';
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | admin_users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
123 row in set (0.11 sec)
mysql> explain select name from admin_users where name = 'ron';
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | admin_users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
123 row in set (0.05 sec)
Upvotes: 3
Reputation: 19879
Unless you need the 'id' column in your result there is no reason to ask for it. MySQL will treat the queries the same. Asking for it may slow things down as MySQL has to return extra data to you and your program will need to parse it. Doubt it matters for an 'id' column, but if it was several huge text fields it might.
Here's what MySQL thinks about the two queries on a simple table of mine (there is an index on the id field).
mysql> explain select id,name from admin_users where id = 1;
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | admin_users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select name from admin_users where id = 1;
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | admin_users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
Upvotes: 1
Reputation: 8809
I suppose I'm asking if it's good practice to put the field from the WHERE statement in SELECT as well even if I won't return it in php.
Nope. This will add network overhead as the data is transfered between client server. If you aren't going to use the data, don't select it.
Upvotes: 1
Reputation: 477100
No, that's not good practice. SELECT precisely those fields which you need.
Upvotes: 1
Reputation: 17451
If you don't need the id returned, don't include it. If you need it, include it.
Upvotes: 1