Summary of several situations in which MySQL indexes fail

Summary of several situations in which MySQL indexes fail

1. Indexes do not store null values

More precisely, single-column indexes do not store null values, and composite indexes do not store values ​​that are all null. Indexes cannot store Null values. Therefore, when the is null condition is used for this column, the index cannot be used because there is no Null value in the index, and the entire table can only be scanned.
Why can't the index column store Null values?
Building a tree of index column values ​​necessarily involves many comparison operations. The special feature of the Null value is that most of the operations involved take the value of null.
In this case, the null value cannot actually participate in the indexing process. That is, null values ​​do not appear in the leaf nodes of the index tree like other values.

2. Not suitable for columns with fewer key values ​​(columns with more duplicate data)

If the index column TYPE has 5 key values ​​and there are 10,000 records, then WHERE TYPE = 1 will access 2,000 data blocks in the table.
In addition to accessing the index blocks, a total of more than 200 data blocks need to be accessed.
If the whole table is scanned, assuming that there are 10 data items in one data block, then only 1,000 data blocks need to be accessed. Since the full table scan accesses fewer data blocks, the index will definitely not be used.

3. Leading fuzzy query cannot use index (like '%XX' or like '%XX%')

Suppose there is a column of code whose values ​​are 'AAA', 'AAB', 'BAA', 'BAB', and if there is a condition where code like '%AB', since the previous condition is ambiguous, the order of the index cannot be used and the values ​​must be searched one by one to see if the condition is met. This will result in a full index scan or a full table scan. If the condition is where code like 'A % ', you can search for the position of the CODE starting with A in CODE. When you encounter data starting with B, you can stop searching because the subsequent data will definitely not meet the requirements. This way you can take advantage of the index.

4. Several situations where index failure occurs

1. If there is an or in the condition, it will not be used even if there is an index in the condition (this is why or is used as little as possible)
If you want to use or and make the index effective, you can only add an index to each column in the or condition.

select * from tb1 where nid = 1 or name = '[email protected]';
 
 
 --Special: The or condition will be invalid only when there is a column that is not indexed. The following will use the index select * from tb1 where nid = 1 or name = 'seven';
   select * from tb1 where nid = 1 or name = '[email protected]' and email = 'alex'

2. For a multi-column index, the index will not be used unless it is the first part to be used

3.Like query starts with %

select * from tb1 where email like '%cn';

4. If the column type is a string, the data must be quoted in the condition, otherwise the index will not be used.

elect * from tb1 where email = 999;

5. If MySQL estimates that a full table scan is faster than an index, it does not use the index.

6. Ordinary index does not mean no indexing

- !=
 select * from tb1 where email != 'alex'
 
 --Special: If it is a primary key, the index will still be used select * from tb1 where nid != 123
->
 select * from tb1 where email > 'alex'
 
 
 --Special: If the primary key or index is an integer type, the index will still be used select * from tb1 where nid > 123
  select * from tb1 where num > 123

7. Leftmost prefix of composite index

If the combined index is: (name, email)

name and email -- using index
name -- use the index
email -- do not use index

The above is a detailed summary of several situations in which MySQL indexes fail. For more information about MySQL index failures, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Analysis of several situations where Mysql indexes fail
  • Common scenarios and avoidance methods for index failure in MySQL
  • Detailed analysis of the situations in which database indexes will fail in MySQL
  • Will mysql's in invalidate the index?
  • Analysis of five situations of MySQL index failure
  • Several methods to solve the problem of MySQL fuzzy query index failure
  • Summary of some common writing methods that cause MySQL index failure
  • Detailed analysis of several situations in which MySQL indexes fail
  • Analysis of several situations where MySQL index fails
  • Detailed explanation of MySQL database indexes and failure scenarios

<<:  Detailed steps to deploy SpringBoot projects using Docker in Idea

>>:  js realizes the non-select drop-down box version of the three-level linkage of provinces, cities and districts

Recommend

Examples of using the or statement in MySQL

1. The use of or syntax in MySQL, and the points ...

Discussion on the way to open website hyperlinks

A new window opens. Advantages: When the user cli...

Django online deployment method of Apache

environment: 1. Windows Server 2016 Datacenter 64...

Sample code for testing technology application based on Docker+Selenium Grid

Introduction to Selenium Grid Although some new f...

Keep-alive multi-level routing cache problem in Vue

Table of contents 1. Problem Description 2. Cause...

Mysql timeline data to obtain the first three data of the same day

Create table data CREATE TABLE `praise_info` ( `i...

How to detect if the current browser is a headless browser with JavaScript

Table of contents What is a headless browser? Why...

Calling Baidu Map to obtain longitude and latitude in Vue

In the project, it is necessary to obtain the lat...

Simple Mysql backup BAT script sharing under Windows

Preface This article introduces a simple BAT scri...

In-depth analysis of MySQL lock blocking

In daily maintenance, threads are often blocked, ...

How to build Nginx image server with Docker

Preface In general development, images are upload...

10 Website Usability Tips Everyone Should Know

Let’s not waste any more time and get straight to...

Quick solution for forgetting MySQL8 password

Preface When we forget the MySQL database passwor...

Detailed explanation of the use of MySQL group links

Grouping and linking in MYSQL are the two most co...

Summary of the differences between MySQL storage engines MyISAM and InnoDB

1. Changes in MySQL's default storage engine ...