Mysql index,

Mysql index,

Index name: you can create an index on multiple columns. This index is called composite index (Composite Index) (composite index)

I. Create an index
Create table t1 (id int primary key, username varchar (20), password varchar (20 ));

Syntax for creating a single index: create index name on table name (field name)

The index name is generally: Table name_field name

Create index t1_id on t1 (id );

Syntax for creating a joint index: create index name on table name (field name 1, field name 2)

Create a joint index for username and password: create index t1_username_password on t1 (username, password)

Ii. Index failure
1. An index is created for a single field. The where condition contains multiple fields.
Select * from template t where t. logicdb_id = 4 and t. sync_status = 1

2. Create a joint index. The where condition is a single field. This is the opposite of the above case.
Select * from template t where t. sync_status = 4

3. Index column operations, including (+ ,-,*,/,! , <>, %, Like '% _' (% Put in Front) (both sides add % not invalid), or, in, exist, etc.), leading to index failure

4. Type Error. For example, if the field type is varchar, the where condition uses number.
Incorrect syntax: select * from template t where t. template_id = 1

Statement: select * from template t where t. template_id = '1'

5. apply internal functions to the index. In this case, create a function-based index.
Select * from template t where ROUND (t. logicdb_id) = 1

6. The efficiency of querying a table is faster than that of applying an index.

7. The is null index is invalid. The is not null Betree index takes effect. The cause is that mysql is not written into the index in null. Depends on the application database.

Iii. query Indexes
Show index from t

Iv. Deleting an index
Drop index idx_col1_col2 on t1