Record a SQL optimization scenario that greatly improves query efficiency

As we all know, how SQL writes, the impact on query efficiency is quite large. Let's look at a more general query:

Suppose a table has Event_date and Event_time2 fields representing the date and time, and now gives you a time string, which is a combination of "date + Time", which now needs to filter out part of the data by the time range. If you don't turn, it's very likely that you will use Concat (Event_date, event_time) to connect functions according to inertial thinking, such as:

SELECT A, MAX (B) as B,substr (CONCAT (Event_date, Event_time), 1,12) as Tran_time from        tablename  WHERE SUBSTR ( CONCAT (Event_date, Event_time), 1,12) >=: Start_time   and SUBSTR (CONCAT (Event_date, Event_time), 1,12) <: End_ Time and       a =: a          GROUP by A,tran_time          ORDER by A,tran_time ASC

Start_time and End_time are strings that are passed over by the "date + time" combination.

This is a relatively easy to think of the traditional thinking, but this SQL statement query efficiency is old low. It takes almost a 4.5s+, because not only does the Max function need to be used, but also by substr (CONCAT (Event_date, Event_time), 1,12), the calculated field group by, most notably in the Where statement, A 2-time field requires multiple function operations to see if those records meet your specified time range ~ ~ Thus, in the unconscious, resulting in a full table scan, the efficiency is very low, especially in the table in the large amount of data.

We already know that if a table is a table with a larger amount of data and is generally used with queries, the use of the index will bring us great performance optimizations. Taking into account the use of indexes, we should try to avoid the fields in the WHERE statement do not function (because even if you are indexed in the Where field, but if you do the function of them, the index is invalid), so we consider to separate them as far as possible.

Based on the above ideas, the corrections are as follows:

Index the three fields of a, evnet_date, and Event_time first.

SELECT A, MAX (B) as B,substr (CONCAT (Event_date, Event_time), 1,12) as Tran_time from        tablename   WHERE Event_date &  gt;=: Start_date and Event_time >=: Start_time and         event_date <=: End_date and Event_time <: End_time        and A =: a          GROUP by A,tran_time          ORDER by A,tran_time ASC

It can be seen that we have event_date and event_time the beginning and end of the time are "removed", so just in the pass-through time string to intercept the response date and time string to the SQL statement.

A test check found that the results of the 2 SQL queries were consistent, stating that they were equivalent.

After the correction, found that the efficiency increased to about 0.05s, performance has been greatly improved!!!

This optimized SQL statement is to break through the SQL statement field to adapt to the external transmission of the time string, but from the perspective of SQL optimization, let external conditions to adapt to SQL statement query, this idea I have little before, also considered a kind of thinking breakthrough!

Record a SQL optimization scenario that greatly improves query efficiency