Several situations that cause MySQL to perform a full table scan

Several situations that cause MySQL to perform a full table scan

In the past two days, I have seen two types of SQL that may cause full table scans. Here are two examples for you to see, hoping to avoid pitfalls:

Case 1:

In the case of forced type conversion, the index will not be used and a full table scan will be performed.

Here are some examples:

First we create a table

 CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `score` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_score` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

We can see that this table has three fields, two of which are of int type and one of varchar type. The varchar type field score is an index, and id is the primary key.

Then we insert some data into this table. The table after inserting the data is as follows:

mysql:yeyztest 21:43:12>>select * from test;
+----+------+-------+
| id | age | score |
+----+------+-------+
| 1 | 1 | 5 |
| 2 | 2 | 10 |
| 5 | 5 | 25 |
| 8 | 8 | 40 |
| 9 | 2 | 45 |
| 10 | 5 | 50 |
| 11 | 8 | 55 |
+----+------+-------+
7 rows in set (0.00 sec)

At this time, we use the explain statement to view the execution of the two SQL statements, which are:

explain select * from test where score ='10';

explain select * from test where score =10;

The results are as follows:

mysql:yeyztest 21:42:29>>explain select * from test where score ='10';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+------+------+------+
| 1 | SIMPLE | test | NULL | ref | idx_score | idx_score | 62 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

mysql:yeyztest 21:43:06>>explain select * from test where score =10;
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | idx_score | NULL | NULL | NULL | 7 | 14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

It can be seen that if we use a varchar type value, the number of rows scanned in the result is 1, and when we use an integer value of 10, the number of rows scanned becomes 7, proving that if a forced type conversion occurs, the index will become invalid.

Case 2:

Reverse queries cannot use indexes and will result in a full table scan.

Create a table test1, whose primary key is score, and then insert 6 records:

CREATE TABLE `test1` (
  `score` varchar(20) not null default '' ,
  PRIMARY KEY (`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql:yeyztest 22:09:37>>select * from test1;
+-------+
| score |
+-------+
| 111 |
| 222 |
| 333 |
| 444 |
| 555 |
| 666 |
+-------+
6 rows in set (0.00 sec)

When we use reverse lookup, the index will not be used. Let's look at the following two SQL statements:

explain select * from test1 where score='111';

explain select * from test1 where score!='111';
mysql:yeyztest 22:13:01>>explain select * from test1 where score='111';
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test1 | NULL | const | PRIMARY | PRIMARY | 62 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql:yeyztest 22:13:08>>explain select * from test1 where score!='111';
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test1 | NULL | index | PRIMARY | PRIMARY | 62 | NULL | 6 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

You can see it, use it! = When used as a condition, the number of rows scanned is the total number of rows in the table. So if we want to use the index, we can't use the reverse matching rule.

Case 3:

Certain or value conditions may result in a full table scan.

First we create a table and insert some data:

CREATE TABLE `test4` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[email protected]:yeyztest 22:23:44>>select * from test4;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | yeyz |
| NULL | yeyz |
+------+------+
5 rows in set (0.00 sec)

The table test4 contains two fields. The id field is an index, and the name field is of varchar type. Let's look at the number of scanned rows in the following three statements:

explain select * from test4 where id=1;

explain select * from test4 where id is null;

explain select * from test4 where id=1 or id is null;
mysql:yeyztest 22:24:12>>explain select * from test4 where id is null;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test4 | NULL | ref | idx_id | idx_id | 5 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql:yeyztest 22:24:17>>explain select * from test4 where id=1;
                      +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+------+------+
| 1 | SIMPLE | test4 | NULL | ref | idx_id | idx_id | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+------+------+
1 row in set, 1 warning (0.00 sec)

mysql:yeyztest 22:24:28>>explain select * from test4 where id=1 or id is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test4 | NULL | ALL | idx_id | NULL | NULL | NULL | 5 | 40.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

It can be seen that using id=1 and id is null alone will only scan one row of records, while using or to connect the two will result in scanning the entire table without using the index.

To summarize briefly:

1. In the case of forced type conversion, the index will not be used and the full table scan will be performed

2. Reverse query cannot use indexes, which will result in a full table scan.

3. Some or value conditions may result in a full table scan.

The above are the details of several situations that cause MySQL to perform a full table scan. For more information about MySQL full table scan, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • How to significantly improve the full table scan speed of InnoDB in MySQL
  • MySQL sorting using index scan
  • Index Skip Scan in MySQL 8.0
  • Detailed examples of full table scan and index tree scan in MySQL

<<:  A quick solution to the problem of PC and mobile adaptation

>>:  CSS realizes that the left side of the webpage column is fixed and automatically adjusts the position when scrolling to the bottom

Recommend

HTML Tutorial: DOCTYPE Abbreviation

When writing HTML code, the first line should be ...

Detailed explanation of the life cycle of Angular components (Part 2)

Table of contents 1. View hook 1. Things to note ...

Implementation of Bootstrap web page layout grid

Table of contents 1. How the Bootstrap grid syste...

Installation and configuration tutorial of Linux virtual machine under Windows operating system

Preface At work, we often need to operate in a Li...

Solve the problem of Navicat for Mysql connection error 1251 (connection failed)

Because what I wrote before was not detailed enou...

How to reset your Linux password if lost

1. The startup menu is to move the cursor to the ...

MySQL NULL data conversion method (must read)

When using MySQL to query the database and execut...

Example of making a butterfly flapping its wings with pure CSS3

Pure CSS3 makes a butterfly flapping its wings, s...

MySQL 8.0.20 winx64 installation and configuration method graphic tutorial

This article shares with you the installation and...

Detailed explanation of creating a data table in MySQL and establishing primary and foreign key relationships

Preface When creating a primary and foreign key f...

Mini Program to Implement Slider Effect

This article example shares the specific code for...

A collection of information about forms and form submission operations in HTML

Here we introduce the knowledge about form elemen...

Some questions about hyperlinks

I am very happy to attend this episode of potato ...

How to run postgreSQL with docker

1. Install Docker. Reference URL: Docker Getting ...

Detailed steps to install the specified version of docker (1.12.6) using rpm

1. Reasons If the system is Centos7.3, the Docker...