About MySQL Index

In database tables, using indexes can greatly improve query speed.
All storage engines support at least-indexes per table and a total index length of at least bytes. Most storage engines has higher limits.


Suppose we create a testindex table:
CREATE TABLE testindex (i_testid INT not null,vc_name VARCHAR (+) not NULL);

we randomly inserted 1000 records into it, one of which
   I_testid  Vc_name
     555  Erquan
    
finding records for Vc_name= "Erquan"
SELECT * from Testindex WHERE vc_name= ' Erquan ';
, if an index has been established on the Vc_name, MySQL does not need any scanning, that is, the record can be found accurately! Instead, MySQL scans all records, that is, to query 1000 times ~ ~ can be indexed to increase the query speed by 100 times times.

I. Index sub-column index and combined index
  Single -column index: That is, an index contains only single columns, and a table can have multiple single-row indexes, but this is not a composite index.
  Composite Index: That is, a cable contains multiple columns.

second, introduce the type of index

   1. Normal index.
     This is the most basic index and it has no limitations. It is created in the following ways:
     (1) Creating index: Create index IndexName on TableName (tablecolumns (length)); if it is a Char,varchar type, length can be less than the actual length of the field, if it is a BLOB and TEXT type, the same length must be specified.
     (2) Modify table structure: ALTER tableName ADD INDEX [IndexName] On (tablecolumns (length))
     (3) directly specify when creating the table: Create TABLE TableName ([...], INDEX [IndexName] (tablecolumns (length));
   
   2. Unique index.
      It is similar to the previous "normal index", except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:
      (1) Creating an index: Create UNIQUE index indexname on tableName (tablecolumns (length))
     (2) Modify table structure: ALTER tableName ADD UNIQUE [IndexName] On (tablecolumns (length))
     (3) directly specify when creating the table: Create TABLE TableName ([...], UNIQUE [IndexName] (tablecolumns (length));

    3. Primary KEY index
      It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built: Create TABLE Testindex (I_testid INT not NULL auto_increment,vc_name VARCHAR (+) not null,primary KEY (i _testid)); Of course, you can also use the ALTER command.
      Remember: A table can have only one primary key.

     4. Full-Text Indexing
       MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. No discussion here, hehe ~ ~

   syntax for dropping an index: Drop index index_name on TableName

three, single-column indexes and combined indexes

   to visually compare the two, build a table:
   CREATE TABLE myindex (I_testid INT NOT NULL auto_increment, Vc_name varchar (.) NOT NULL, vc_city varchar (+) not NU LL, i_age int not NULL, i_schoolid int NOT null, PRIMARY KEY (I_testid));

   in these 10,000 records, 7 of the 8 are distributed in 5 vc_name= "Erquan" records, but City,age,school combinations are different.
 look at this T-sql:
   SELECT I_testid from Myindex WHERE vc_name= ' Erquan ' and vc_city= ' Zhengzhou ' and i_age=25;

   first consider building a single-column index:
   an index was established on the Vc_name column. When executing T-SQL, MySQL quickly locks the target on the 5 records of Vc_name=erquan and takes it out to a middle result set. In this result set, the first rule out vc_city not equal to "Zhengzhou" record, and then exclude i_age not equal to 25 of the record, and finally filtered out the only qualified records.

   Although the index is built on the vc_name, MySQL does not have to scan the whole table when querying, but the efficiency is improved, but there is a certain distance from our request. Similarly, the efficiency of a single-column index established separately in vc_city and I_age is similar.

   to further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build the Vc_name,vc_city,i_age into an index:
   ALTER table Myindex ADD INDEX name_city_age (Vc_name (Ten), vc_city,i_age);--note that when the table is built, the vc_name length is 50, why use 10 here? Because the length of the name does not typically exceed 10, this speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.

   when executing t-SQL, MySQL does not need to scan any records to find a unique record!!

   There must be someone to ask, if you set up a single-column index on the vc_name,vc_city,i_age, so that the table has 3 single-column index, query and the above combined index efficiency? Hey, big different, far below our portfolio index ~ ~ Although there are three indexes at this time, MySQL can only use one of the single-column indexes which it considers to be the most efficient.

   The establishment of such a composite index is actually equivalent to establishing a separate
       Vc_name,vc_city,i_age
       vc_name,vc_city
       Vc_name
   such a combination of three indexes! Why is there no such combination index as vc_city,i_age? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. It is not just that the combined index is used for queries that contain these three columns, and several of the following T-SQL is used:
   SELECT * from Myindex whree vc_name= "Erquan" and vc_city= "Zhengzhou"
   SELECT * from Myindex whree vc_name= "Erquan"
and the next few are not used:
   SELECT * from Myindex whree i_age=20 and vc_city= "Zhengzhou"
   SELECT * from Myindex whree vc_city= "Zhengzhou"

Another example:

Suppose that a table has the the following specification:

CREATE TABLE Test (

   ID       INT not NULL,

   last_nameCHAR (+) is not NULL,

   first_name CHAR (+) not NULL,

   PRIMARY KEY (id),

   INDEX name (last_name,first_name)

);

The name index is a index over the last_name and first_name columns. The index can is used for queries that specify values in a known range for last_name, or for both last_name and first_name . Therefore, the name index is used in the following queries:

SELECT * FROM Test WHERE last_name= ' Widenius ';

 

SELECT * FROM Test

 WHERE last_name= ' Widenius ' and first_name= ' Michael ';

 

SELECT * FROM Test

 WHERE last_name= ' Widenius '

 and (first_name= ' Michael ' OR first_name= ' Monty ');

 

SELECT * FROM Test

 WHERE last_name= ' Widenius '

 and first_name >= ' M ' and first_name < ' N ';

However, the name index is not used in the following queries:

SELECT * FROM Test WHERE first_name= ' Michael ';

 

SELECT * FROM Test

 WHERE last_name= ' Widenius ' OR first_name= ' Michael ';


iv. use of indexes
   should you build and use the index here? But under what circumstances do you need to index it? In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like (explained later).
   SELECT t.vc_name from Testindex T left joins Myindex m on T.vc_name=m.vc_name WHERE m.i_age=20 and m.vc_city= ' Zhengzhou ' , there is a need to index the vc_city and i_age of the Myindex table, because the vc_name of the Testindex table is now in the join clause, and it is necessary to index it.

   just now, you only need to index the like at some point? Yes. Because MySQL does not use indexes when querying with wildcards% and _, such as
   SELECT * from Myindex WHERE vc_name like ' erquan% '
will use the index, and
   SELECT * from Myindex wheret vc_name like '%erquan '
   The index is not used.


v. Deficiencies of the index

   It says so many good words in the index, does it really have as great as the legend? Of course there will be shortcomings.

   1. Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file

   2. Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly.