MySQL Select 1

Look at the database connection pool source code, found that the connection pool parameter validationquery (SQL query, used to verify the connection removed from the connection pool) set the value of "Select 1", which was seldom used in this notation, so

Google for a moment, summarized as follows:

Take the Seven_user table as an example, the following is the build table statement:

CREATE TABLE ' Smily_test ' (
' id ' int (one) not NULL auto_increment,
' Version ' int (4) not NULL DEFAULT ' 0 ',
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=6 DEFAULT Charset=utf8;

Select 1;
Select 1 from Smily_test;
Select ID from Smily_test;
Select version from Smily_test;
SELECT * from Smily_test;

The result of the above SQL statement execution is as follows:

Mysql> Select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in Set (0.00 sec)

Mysql> Select 1 from Smily_test;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
+---+
4 rows in Set (0.00 sec)

Mysql> select ID from smily_test;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in Set (0.00 sec)

Mysql> select version from Smily_test;
+---------+
| Version |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
+---------+
4 rows in Set (0.00 sec)

Mysql> select * from Smily_test;

+----+---------+
| ID | Version |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+---------+
4 rows in Set (0.00 sec)

Use show profiles to view the execution efficiency of these SQL statements;

Mysql>show Profiles;+----------+------------+--------------------------------+| query_id | Duration | Query |+----------+------------+--------------------------------+|1|0.00017700|Select 1||2|0.00013400|Select 1  fromSmily_test | |3|0.00011000|SelectId fromSmily_test | |4|0.00010500|SelectVersion fromSmily_test | |5|0.00011700|Select* fromSmily_test |+----------+------------+--------------------------------+5Rowsinch Set(0.00Sec

The effect is no difference, is to check whether there is a record, is generally used for conditions.

See execution efficiency as: Select 1 > select 1 from Table > select Anycolum from Table > select * FROM table

Take a look at the efficiency of Count (*) and COUNT (1).

+----------+------------+------------------------------------+| query_id | Duration   | Query                              |+----------+------------+------------------------------------+|        7 0.00037200 Select count (1 from smily_test    | |        8 0.00034500 Select  from Smily_test    |+----------+------------+------------------------------------+

MySQL Select 1