Mysql 'where index column = xx and AMISOM index column = yy' sometimes does not take the index? - Codes Helper - Programming Question Answer

Mysql 'where index column = xx and AMISOM index column = yy' sometimes does not take the index?

for example, I always thought that select * from table where index column = xx and AMISOM column = yy must take the index of index column.
but today I found that is not the case .
tested (MySQL 5.6.16):

when the union condition can match to the record, goes to the index.
when the union condition cannot match to the record, does not take the index.

according to my expectation, the mysql optimizer scans with the index column and does not find it. Just return it directly, regardless of the non-index column behind it.
however, according to the above test, it is very possible to go to the index column first and find that there is no corresponding record, and then scan the whole table.

in addition, even if the index of the force index (index column is used) is still the case above.

< H2 > example: < / H2 >

id is an indexed column, and content_type is a non-indexed column.

when there is no record for joint condition matching

prifiling

SQL:
select sql_no_cache * from force index(primary) where id=5 and content_type=""; ()

profile:

Mysql--configure mysql-Index mysql-optimize mysql

Jan.18,2022

Open profiling and then execute it. explain may not be the same as the actual execution. From the screenshot, id is the primary key and the query is equivalent, so it is right to go to the index. Take out the table structure and have a look at it

.

profiling opening method

-sharp 
set profiling = 1

-sharp 
...

-sharp 
show profiles

-sharp 
show frofile for query query_number

= reply to split line =

there is a mistake in the above. Profiling can see that the execution uses resources, but cannot see whether or not to use indexes

I executed this query under version 5.7 (which happens to be available in the test environment):

Extra no matching row in const table

explain

means that the corresponding data cannot be found in the unique index. It is said in the document that the index is used to query. As for how to prove it, I have not thought of

.

how can you tell whether an index is used or not in your example? Your id and content_type columns belong to the index content of the primary key index, that is, your query is conducted in a const table, so no data can be found will display Impossible WHERE noticed after reading const tables. In other cases, using where is generally displayed.
the Impossible WHERE here tells you that if you don't find the row you want in the primary key index, the primary key is also an index, so don't discriminate against him, buddy.
in addition, if you want to do an experiment, you should do it with a non-primary key index.

Previous: Axios's cancelToken is not based on the abandoned cancelable promises proposal.

Next: How to improve the accuracy of wordpress search? The keywords searched by the user only match the title and content!

Menu