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

Detailed tutorial on deploying Django project under CentOS

Basic Environment Pagoda installation service [Py...

Teach you how to use charAt() in JavaScript to count the most frequently appearing characters and their number of occurrences

Previously, I introduced the use of the charAt() ...

MySQL 5.7.21 decompression version installation and configuration method graphic tutorial (win10)

The installation and configuration method of MySQ...

Linux (center OS7) installs JDK, tomcat, mysql to build a java web project running environment

1. Install JDK 1. Uninstall the old version or th...

Detailed examples of converting rows to columns and columns to rows in MySQL

mysql row to column, column to row The sentence i...

How to connect to MySQL using C++

C++ connects to MySQL for your reference. The spe...

js development plug-in to achieve tab effect

This article example shares the specific code of ...

Summary of common docker commands

Docker installation 1. Requirements: Linux kernel...

How to insert weather forecast into your website

We hope to insert the weather forecast into the w...

MySQL Basics Quick Start Knowledge Summary (with Mind Map)

Table of contents Preface 1. Basic knowledge of d...

About the correct way to convert time in js when importing excel

Table of contents 1. Basics 2. Problem Descriptio...

Analysis of the HTML writing style and reasons of experienced people

1. Navigation: Unordered List vs. Other Label Ele...

Detailed explanation of the example of Connect on the Socket (TCP) Client side from the Linux source code

Preface The author has always felt that it would ...

Solve the cross-domain problem of get and post requests of vue $http

Vue $http get and post request cross-domain probl...

JavaScript to achieve fixed sidebar

Use javascript to implement a fixed sidebar, for ...