MySQL Force index use

In the company backstage a module function log has a search function, through the previous period of time product use time interval search feedback some lag, I found this search function is relatively slow, to 3 seconds or so to come out, decided to do a bit of optimization here.

The core problem with parsing code and SQL discovery is that an interval query takes too long and takes more than 2 seconds, so I decided to see if this could be optimized, with core SQL

SELECT  *  fromXxx_logWHERE(' CTime 'between '2017-09-11 09:34:13'   and '2017-10-11 09:34:13') andId> 27851 andColumn1= 'XXX' and(Column2= 'NULL' ORLENGTH (Column2)>  the)ORDER  byIdDESCLIMIT0, -

This query is a simple query, no linked table, is a single table of the limit paging query, plus a time interval and field search, I through the SQL analysis explain found not go index, scan interval is also very large, because the table has nearly 1 million records, the query scanning interval of nearly 500,000, I feel that this must be inefficient.

Then queried the data, found that time-consuming indeed in about 2.6 seconds, I initially conceived that the Column2 field query must be unable to change, after all, it involves the previous business, the field so search query is really counterintuitive, but since the previous function has been designed so, now to do the adjustment, But there may be problems, then only in the ID ctime column1, id it to write dead, set a record to start the query, I guess the previous data is test data or discarded data, since there is a ruler, do not move it, Column1 is a fixed query value, This can not be changed, such a fixed value in the data row difference is not obvious, index effect is not big, then is CTime, the only active article is only it.

Then I analyzed the module to achieve the function of logic, is to query the time to meet the requirements of the data, this period of time, is obviously one months, can set this query interval can only be checked for one weeks?

I changed to a week time interval query, certainly can reduce the query interval ah, but unfortunately, this reduced query interval, and did not have much effect, finally helpless, I added a normal index btree index, CTime Index, I thought everything would be better, Who knows, after explain effect still like that!

may use the index keys CTime, however, the query interval is close to 450,000, the effect is not obvious, the query time is still more than 2 seconds, this can let me puzzled, a moment helpless, think for a while, suddenly think of this feeling is the index does not really take effect caused, So can you force me to tell MySQL to use an index, let him take the initiative to go to an index, and then I queried the data to find the Force index (the index forced to go), I immediately tried

SELECT  *  fromXxx_log ForceIndex(CTime)WHERE(' CTime 'between '2017-09-11 09:34:13'   and '2017-10-11 09:34:13') andId> 27851 andColumn1= 'XXX' and(Column2= 'NULL' ORLENGTH (Column2)>  the)ORDER  byIdDESCLIMIT0, -

Sure enough, the CTime index was used after explain analysis.

and the query interval reduced to more than 30,000, this effect is too obvious, time-consuming down to 0.2 seconds, then this function immediately from 3 seconds to open down to 0.3 seconds can be opened, this optimization effect I am very satisfied.

When discussing the table's data with OPS, the OPS provider said that the data volume of the table was nearly 1 million, and that the time was 2016 years in 2015, he proposed to transfer 2015 2016 years of data, I looked at the 2015 and 2016 data totals more than 700,000, Occupy the majority, if the transfer, it is really helpful for us to inquire ah, than right ah, update ah, and so on, because this is a log table, in a period of time, after all, most of the year will not see the year before the query data, so I think this practice also has the basis, also reasonable, coincides with the colleague leave to rest The matter will be discussed again when he comes back.

The thought of forcing the use of indexes, but also a light, a little bit of luck in the inside, or it is really not good to solve this problem, is the weather cooperated it! Then the feeling of a song:

The boundless sky shows the divinity,

The highway Cui Wei face like ash.

And now,

The frontier of the town!

MySQL Force index use