How MySQL builds the index

1. Select the data type of the index

MySQL supports many data types, and choosing the right data type to store data has a significant impact on performance. In general, you can follow some of the following guidelines:

(1) Smaller data types are generally better: smaller data types typically require less space in disk, memory, and CPU caches, and are processed faster.
(2) Simple data types are better: integer data is less expensive to handle than characters, because string comparisons are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.
(3) Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.


2 MySQL indexed commands


1.PRIMARY  KEY(primary key index) MySQL>ALTER  TABLE' table_name 'ADD  PRIMARY  KEY(  `column`  ) 2.UNIQUE(unique index) MySQL>ALTER  TABLE' table_name 'ADD  UNIQUE (`column` ) 3.INDEX(normal index) MySQL>ALTER  TABLE' table_name 'ADD  INDEXIndex_name ('column`  )4. Fulltext (full-text index) MySQL>ALTER  TABLE' table_name 'ADDFulltext ('column` )5. Multi-column index MySQL>ALTER  TABLE' table_name 'ADD  INDEXIndex_name (' Column1 ', ' column2 ', ' column3 ')

How MySQL builds the index