2014 Summary of SQL statement optimization

Before taking over a data statistics processing of small programs, originally logically and nothing, just a little bit of data, some of the table data reached the Tens do not. Because it is statistical, so inevitably various connections to various queries, the results of this small program after the completion of the run once to 1 hours. It's a bit unexpected, so start optimizing.

1. Add an index

Usually do not pay attention to or the amount of data is small, may ignore this, plus or no difference, but when the amount of data is very large when the difference is very obvious, no index, in 10,000 or more than 100,000 of the data in the time difference between the query can be identified. There are 2 points to pay special attention to:

1) By default we create a table, and the database automatically adds a default index to us, but when you create a table with the created tables table name as SELECT statement, the database is not automatically indexed, and we need to add the index manually.

2) which fields need to be indexed? Those fields that act as bridges (joins) in our query statements need to be indexed. Because index fields are included in the query criteria to enjoy the speed increase of the index.

It can be said that the index is the most obvious and easiest way to improve the query speed, it can make a few minutes of the query in milliseconds to complete, the efficiency is not a little bit higher.

2. Narrowing the Query collection

Since it is a query, there must be a collection of queries, that is, the collection after the from. If you can narrow this set, then the time of the query traversal will be reduced a lot, the overall query time will naturally decrease. There are 2 main means:

1) Create a temporary query table. If a table has a large amount of data, and we only query in a collection of data that satisfies certain criteria, then we can first query from this table for all datasets that meet certain criteria to merge and create a table, a typical application is create TABLE XX as Select xx where xx, so that we get a much smaller than the original table of the temporary table, then all the query work from this new table can be.

2) optimize the order of query statements. Where the conditional statement is executed from right to left, so we can put more data can be excluded from the condition of the rightmost, so that the rest of the data collection will be smaller, the next condition query will be faster. The same idea, when we use subqueries, Allowing some subqueries to exclude more data will also improve overall efficiency.

3) to go to the heavy, go back to the original is a relatively time-consuming operation, but if a collection is used repeatedly, then the collection of re-processing will also lead to efficiency improvements.

After optimization, the applet executes within 5 minutes. The improvements to the SQL statement can be seen to be significant. Of course, because my small program is not very good at the beginning, so the optimization of a lot of improvement, but this also shows that if you pay attention to the optimization of SQL statements, in the process of processing more attention, to a certain extent can avoid many performance problems.

2014 Summary of SQL statement optimization