In mysql !=, should I use the index?
Many people wonder whether != should be indexed. It can be definitely said that this operation can be indexed, but in actual situations, why is it not indexed? First of all, we need to know that indexing has a great relationship with the amount of data and data trend (cardinality). If the table itself has hundreds of records, there is not much difference between indexing and table scanning. It may even be worse when there is a bookmark jump. Table scanning is more efficient. Indexing may not be used at this time. In addition, the proportion of the result set obtained after the != operation in the total result set is also a key factor. If the returned result set is too large (more than 20%), then it may It will not use the index, but choose a more efficient table scan.
Let’s take a look at an example:
CREATE TABLE `b` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` bigint(20) DEFAULT 50,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB
insert into b (name) values('a'),('b'),('c'),('d'),('e'),(' f'),('g'),('h'),('i');
--Multiple executions
insert into b (name)
select 'k' from b ;
select name, count(*)
from b
group by name
explain select * From b where name !='k';
explain select * From b where name !='a';
Obviously we see from statistics that the value of K is very large, so our !=’k’ data set only has 8 items, which accounts for a small proportion of the total data and can be indexed very well. If k is replaced with a, the index will not be used and it will become a full table scan
MYSQL series books
Highly available mysql: https://url41.ctfile.com/f/49289241-959127723-de4738?p=2651 (Access password: 2651)
MySQL King’s Road to Advancement.pdf: https://url41.ctfile.com/f/49289241-959127432-204284?p=2651 (Access password: 2651)
MySQL Technology Insider InnoDB Storage Engine 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p=2651 (Access password: 2651)
MySQL Technology Insider 4th Edition.pdf: https://url41.ctfile.com/f/49289241-959125506-a5bcec?p=2651 (Access password: 2651)
MySQL management, performance tuning, high availability and monitoring (Second Edition).pdf: https://url41.ctfile.com/f/49289241-959124249-d59f54?p=2651 (Access password: 2651)
In-depth explanation of MySQL database development, optimization, management and maintenance 2nd edition.pdf: https://url41.ctfile.com/f/49289241-961464090-68bf10?p=2651 (Access password: 2651)
High Performance MySQL. 3rd Edition.Baron Schwartz.pdf: https://url41.ctfile.com/f/49289241-961462308-52cc5d?p=2651 (Access password: 2651)
MYSQL Kernel: INNODB Storage Engine Volume 1.pdf: https://url41.ctfile.com/f/49289241-961461357-ee63e3?p=2651 (Access password: 2651)
MySQL Technology Insider InnoDB Storage Engine 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959126379-4590a8?p=2651 (Access password: 2651)
MySQLDBA training.pdf: https://url41.ctfile.com/f/49289241-961459500-9b201d?p=2651 (Access password: 2651)
MySQL5.7 from beginner to proficient.pdf: https://url41.ctfile.com/f/49289241-961459329-48cbcf?p=2651 (Access password: 2651)
Highly available mysql.pdf: https://url41.ctfile.com/f/49289241-959127723-de4738?p=2651 (Access password: 2651)
HIVE e-book
Practical Hive.pdf: https://url41.ctfile.com/f/49289241-959129883-d35ee9?p=2651 (Access password: 2651)
Hive-Succinctly.pdf: https://url41.ctfile.com/f/49289241-959129709-30f30b?p=2651 (Access password: 2651)
Apache Hive Essentials.pdf: https://url41.ctfile.com/f/49289241-959129691-b1a4aa?p=2651 (Access password: 2651)
Apache Hive Cookbook.pdf: https://url41.ctfile.com/f/49289241-959129619-3a8ea6?p=2651 (Access password: 2651)
hadoop e-book
Practical Hadoop Migration.pdf: https://url41.ctfile.com/f/49289241-959131470-dd3e24?p=2651 (Access password: 2651)
Hadoop in action – Lu Jiaheng (full HD version).pdf: https://url41.ctfile.com/f/49289241-959131365-433ec9?p=2651 (Access password: 2651)
Hadoop & Spark big data development practice.pdf: https://url41.ctfile.com/f/49289241-959131032-ba40ea?p=2651 (Access password: 2651)
Expert Hadoop Administration.pdf: https://url41.ctfile.com/f/49289241-959130468-ba70cd?p=2651 (Access password: 2651)
Big Data Forensics – Learning Hadoop Investigations.pdf: https://url41.ctfile.com/f/49289241-959130435-9ab981?p=2651 (Access password: 2651)
python e-book
python learning manual.pdf: https://url41.ctfile.com/f/49289241-959129403-5b45b1?p=2651 (Access password: 2651)
Python Basics Tutorial – 3rd Edition.pdf: https://url41.ctfile.com/f/49289241-959128707-de6ef2?p=2651 (Access password: 2651)
Python programming: from entry to practice.pdf: https://url41.ctfile.com/f/49289241-959128548-ce965d?p=2651 (Access password: 2651)
Python Projects for Beginners.pdf: https://url41.ctfile.com/f/49289241-959128461-b53321?p=2651 (Access password: 2651)
kafka e-book
Learning Apache Kafka, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959134953-a14305?p=2651 (Access password: 2651)
Kafka authoritative guide.pdf: https://url41.ctfile.com/f/49289241-959134932-295734?p=2651 (Access password: 2651)
Kafka in Action.pdf: https://url41.ctfile.com/f/49289241-959134116-12111a?p=2651 (Access password: 2651)
Apache Kafka in action.pdf: https://url41.ctfile.com/f/49289241-959133999-76ef77?p=2651 (Access password: 2651)
Apache Kafka Cookbook.pdf: https://url41.ctfile.com/f/49289241-959132547-055c36?p=2651 (Access password: 2651)
spark e-book
Spark best practices.pdf: https://url41.ctfile.com/f/49289241-959415393-5829fe?p=2651 (Access password: 2651)
Data algorithm–Hadoop-Spark big data processing skills.pdf: https://url41.ctfile.com/f/49289241-959415927-5bdddc?p=2651 (Access password: 2651)
Spark big data analysis in practice.pdf: https://url41.ctfile.com/f/49289241-959416377-924161?p=2651 (Access password: 2651)
Spark 2.0 for Beginners.pdf: https://url41.ctfile.com/f/49289241-959416710-7ea156?p=2651 (Access password: 2651)
Pro Spark Streaming.pdf: https://url41.ctfile.com/f/49289241-959416866-6116d7?p=2651 (Access password: 2651)
Spark in Action.pdf: https://url41.ctfile.com/f/49289241-959416986-e759e9?p=2651 (Access password: 2651)
Learn PySpark.pdf: https://url41.ctfile.com/f/49289241-959417049-ac04a0?p=2651 (Access password: 2651)
Fast Data Processing with Spark.pdf: https://url41.ctfile.com/f/49289241-959417157-8ec3b0?p=2651 (Access password: 2651)
Fast Data Processing with Spark, 2nd Edition.pdf: https://url41.ctfile.com/f/49289241-959417211-856d08?p=2651 (Access password: 2651)
OReilly.Learning.Spark.2015.1.pdf: https://url41.ctfile.com/f/49289241-959417292-90c1bc?p=2651 (Access password: 2651)
High Performance Spark.pdf: https://url41.ctfile.com/f/49289241-959417439-7e7893?p=2651 (Access password: 2651)
Machine Learning with PySpark.pdf: https://url41.ctfile.com/f/49289241-959417580-5941b3?p=2651 (Access password: 2651)
Spark for Python Developers.pdf: https://url41.ctfile.com/f/49289241-959417721-d59fbe?p=2651 (Access password: 2651)
Spark Cookbook.pdf: https://url41.ctfile.com/f/49289241-959417811-19c75d?p=2651 (Access password: 2651)
Big Data Analytics with Spark.pdf: https://url41.ctfile.com/f/49289241-959417907-41dbce?p=2651 (Access password: 2651)
PySpark SQL Recipes.pdf: https://url41.ctfile.com/f/49289241-959417970-c23242?p=2651 (Access password: 2651)
Advanced Analytics with Spark Patterns for Learning from Data at Scale .pdf: https://url41.ctfile.com/f/49289241-959417997-a5e3f5?p=2651 (Access password: 2651)
OReilly.Advanced.Analytics.with.Spark.Patterns.for.Learning.from.Data.at.Scale.pdf: https://url41.ctfile.com/f/49289241-959418024-2ff34c?p=2651 (Access password : 2651)
Big Data Analytics Beyond Hadoop_ Real-Time Applications with Storm, Spark, and More Hadoop Alternatives.pdf: https://url41.ctfile.com/f/49289241-959418042-581fb9?p=2651 (Access password: 2651)
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 77455 people are learning the system