The issue of indexing failures in the logging database

Yesterday, a very interesting question, a SQL statement, plus softuseline like '%oqc% ', the speed is particularly slow. After removing the condition, the speed is getting up.

Looking at the execution of the SQL statement, the logical reads of the Softwaredetailinfo table became 1,300,374 times after the query condition was added.

But this query word Ji Mingming has been added Index_softwaredetail index, how can the logical reading is still so large. Look at the details of SQL profile, find the SQL statement, and do not go to that index.

So, with the WITH (Index (index_softwaredetail)), let SQL have to go through this index to try. The SQL query immediately becomes faster. SQL profile shows that the Index_softwaredetail index has been gone. It appears that there is a problem with index creation, resulting in a query plan that does not go through the index, but rather a full table scan. This causes the query to become slower.

All indexes are then re-adjusted. It's OK.

　　When does the index in the database expire?

Summarize the problem of index invalidation in the database, but some have not been tested. This is for your own reference only.

First, the so-called invalidation. It's not really the index that was deleted. Instead, in some cases, the DBMS does not retrieve the index list. The execution speed is the same as the speed without this index. But then another statement is executed. The same index works correctly. So the invalidation of an index is for an SQL statement, a query condition, not the index itself.

Which type of statement executes when the index does not work. Summarized as follows:

1. When the index field is queried for a null query. That is, when the index field is judged to be null. The statement is either NULL or is not NULL.

For example: SELECT * from Softwaredetailinfo where createtime is null this does not retrieve the index table on the Time field. That is, the index was invalidated when this statement was executed.

And then execute.

SELECT * from softwaredetailinfo where createtime = ' 2015-04-11 00:00:00 ' the index table is retrieved at this time. The index works again.

2. When you make a like query on an indexed field. For example: SELECT * from Softwaredetailinfo where softuseline like '%oqc% '. However, some examples on the internet say that the like ' xx% ' index works. I haven't tried.

3. Determine if the index column is not equal to a value. '! = ' operator. Example: SELECT * from Softwaredetailinfo where softuseline! = 0

4. Perform an operation on the indexed column. The operations here include +-*/and other operations. Also includes the use of functions. Like what:

SELECT * from Softwaredetailinfo where softuseline +0= 0

The index does not work at this time.

SELECT * from Softwaredetailinfo where count (softuseline) = 0

The index also does not work at this time.

This means that if the Index field column is not directly judged, the index column index after the operation or other function processing does not work.

5. The leading column in the composite index is not being used as a query condition. For example: Index_softwaredetail index contains (a,b,c) three columns, but in the query condition, there is no A, b column, only column C, then the Index_softwaredetail index does not work, but with a or a, B or a, B, C can use the index as a condition, that is, the first index field must be used, and the query is the index to be valid.

The issue of indexing failures in the logging database