How to apply Index in mysql

 

Without Indexes :

500 Records in table

explain 

select  *  from customers 

where id=42 ;

# id, select_type,  table,      type,  possible_keys, key  , key_len , ref,  rows, Extra
'1',  'SIMPLE',   'customers', 'ref',   'NULL',      'NULL', 'NULL',  'NULL', '500', ''
 

Retrive data from table .I will search all row in table after It will give output.This process take lot of time and lock table and consuming lots of resources .

 

 With Index

mysql>alter table customers add column ID int   ;

mysql>create index ID on customers (ID);

explain 

select * from customers  where id=42

with Indexes retrieve data from table .I will search only one row in table and this process is very fast .

Note:- if you are create  primary key in table so it will create by default index in table. 

ex:  Alter table daily_summary_data  add column ID int not null primary key auto_increment   

IF You have create already table so you want to add primary key in existing column .

MYSQL>alter table day_daily_summary_data_final add unique  (datestationid)  ;

MYSQL> explain
                select * from day_daily_summary_data_final
                where datestationid='202002289041'
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, day_daily_summary_data_final, const, datestationid_2,datestationid, datestationid_2, 18, const, 1,

How to apply index on multiple column

When ever we are created index on multiple column show we have to use unique key .

CREATE TABLE IF NOT EXISTS employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY unique_email (email)
); 

If you want the combination of first_name, last_name, and  phone is also unique contacts so we are using unique index on employee table.

 #create unique index unique_index on employee (first_name,last_name,email);  

#SHOW CREATE TABLE employee;

MYSQL>insert into employee (first_name,last_name,phone,email)

values ('A','B','100-111,'tom@gmail.com') ;

 MYSQL> select * from employee ; 

             # id, first_name, last_name,   phone,    email
                 '1', 'A',                 'B',         '00-111',  'tom@gmail.com'
If you are insert first and last name use in email letter so it is give Duplicate error.

MYSQL> insert into contacts (first_name,last_name,phone,email)  values ('A','B','00-111','B@gmail.com')   

 Error Code: 1062. Duplicate entry 'B@gmail.com' for key 'unique_email'    0.077 sec