For large systems, Oracle,sqlserver is undoubtedly the best choice to look at the growing number of small web sites, they do not have their own servers, just to buy other people's space and database, but this small database on the performance of course and the large database does not contrast, But the small database also wants to own optimization way, today and everybody shares the MySQL to add does not add the single quotation mark the huge difference, for MySQL performance optimization is very meaningful.
As we have just said, life will inevitably have some unpleasant, for example, we use a string type of field as the primary key, on the surface, this is not good, however, the fact that this is useful. The problem comes out, and it takes a hundredfold to add single quotes and no queries to the query statement.
Test table:
Copy Code code as follows:
CREATE TABLE ' foo ' (' key ' varchar () not NULL, ' time ' int (one) not NULL, PRIMARY key (' key ')) Engine=myisam DEFAULT CHARS Et=utf8;
Then insert the 30多万条 data, and then execute the following SQL statement:
Copy Code code as follows:
SELECT *from ' foo ' WHERE ' key ' =1293322797
The query takes 0.1288 seconds, takes about a long time, and then, adds a single quote to 1293322797:
Copy Code code as follows:
SELECT *from ' foo ' WHERE ' key ' = ' 1293322797 '
The query takes 0.0009 seconds, basically 100 times times the difference!!! In other words, without single quotes MySQL performance loss of 100 times times, very shocking proportions!
Later, explain ran the above two statements, see the following two pictures:
When there are no single quotes
When you have single quotes
Obviously, without using single quotes, the primary index is not used, and a full table scan is performed, using single quotes to use the index.
Later I tested with greater than, and returned the same result set, and their time consuming is the same as above, with the explain test, as well as above
Copy Code code as follows:
SELECT *from ' foo ' where ' key ' >1293322797select *from ' foo ' where ' key ' > ' 1293322797 '
Adding single quotes and no single quotes is such a big difference! Is that it will have such a big impact on MySQL performance.
Later, I changed the field ' key ' to int type, then, without single quotes, there is no difference, explain show they are also able to use the main index, but Key_len shortened.
This is all, to sum up, we write SQL query when the time is still tired of adding single quotes, it seems that there is no harm.
