Table data is 200 w, has added index for field code, in more than 40 seconds, query speed is slow, is a single table query, we hope the great spirit to guide the
Select
Count (*)
The from table1
Where the substring (code, 3, 6) NOT in (' 120060 ')
And the substring (code, 3, 3) not in (' 060 ')
And the time between '2020-01-01 00:00:00' and '2020-03-01 00:00:00'

CodePudding user response:

Filter field and add function has not been index on use,
If the query conditions cannot be modified only consider to find a way to on time, in the time field, for example to add index and reduce the time range query many times.

CodePudding user response:

The substring (code, 3, 6) NOT in (' 120060 '), the parameters of the code is coming? So, intercepting layer processing operation in code, code and index, and there is only one value, in use the equals sign, time plus index

CodePudding user response:

To table1 redundant substring (code, 3, 6) and the substring (code, 3, 3) the two fields, and to give the two fields and indexes, and then I forget not walk not to walk in the index, you can try first

CodePudding user response:

The substring and not have made you can't use has been created in Code index
The following scheme can rapidly improve the efficiency of query
Add redundancy to table column: Code1 (used to store the substring (code, 3, 6)) and Code2 (used to store the substring (code, 3, 3))
Give Code1 and Code2 create index
When the query directly use Code1 and Code2 to compare
Will Not In (' 060 ') and Not In (' 120060 ') In the Code2 & lt;> '060' and Code1 & lt;> '120060'

Of course, can also be Code1 and Code2 storage substring (code, 3, 6) and the substring (code, 3, 3) crc32 value, to compare crc32 values

CodePudding user response:

Is there a way to try me? Should be effective