Mysql index Comparison Analysis

Mysql indexes greatly improve the query efficiency. However, not all cases require mysql indexes. Let's analyze the usage of mysql indexes.

Mysql tables have two types of keys: primary key and secondary key.

For example:

 
 

  
  
CREATE TABLE layout_test (  
  
  
 
  
  
   col1 int NOT NULL,  
  
  
 
  
  
   col2 int NOT NULL,  
  
  
 
  
  
   PRIMARY KEY(col1),  
  
  
 
  
  
   KEY(col2)  
  
  
 
  
  
);  
 
 

Col1 is the primary key, and col2 is the secondary key. They create mysql indexes.

This is short for pk and sk.

Myisam:

In myisam, the primary key and sk data are stored in the same way. It is the row number stored on the leaf node of B-tree, that is, the pointer of the Data row.

Innodb:

It is not the same in innodb.

The primary key exists as a clustered index and stores the primary key value and other column values, transaction IDs, and rollback pointers on the leaf node of B-tee. The primary index of innodb is a clustered index)

The leaf node of sk is also different from myisam. It stores the value of primary key instead of the row pointer of data ).

The index can be used only for queries with the leftmost prefix.

The following section is from the mysql user manual:

 
 

  
  
mysql> CREATE TABLE test (  
  
  
           id INT NOT NULL,  
  
  
           last_name CHAR(30) NOT NULL,  
  
  
           first_name CHAR(30) NOT NULL,  
  
  
           PRIMARY KEY (id),  
  
  
           INDEX name (last_name,first_name)); 
 
 

Mysql index name is an index on last_name and first_name. This index will be used to query the specified value within a known range of last_name, last_name, and first_name. Therefore, the name index will be used in the following queries:

 
 

  
  
Mysql> SELECT * FROM test WHERE last_name = "Widenius ";
  
  
 
  
  
Mysql> SELECT * FROM test WHERE last_name = "Widenius"
  
  
AND first_name = "Michael ";
  
  
 
  
  
Mysql> SELECT * FROM test WHERE last_name = "Widenius"
  
  
AND (first_name = "Michael" OR first_name = "Monty ");
  
  
 
  
  
Mysql> SELECT * FROM test WHERE last_name = "Widenius"
  
  
AND first_name> = "M" AND first_name <"N ";
  
  
However, the name index is not required in the following query:
  
  
 
  
  
Mysql> SELECT * FROM test WHERE first_name = "Michael ";
  
  
 
  
  
Mysql> SELECT * FROM test WHERE last_name = "Widenius"
  
  
OR first_name = "Michael ";
  
  
 
 
 

MySQL index usage

Insufficient mysql Indexes

Implement ADO connection to MYSQL

Introduction to mysql trigger new old

Create MySQL trigger syntax