Optimizing MySQL performance through indexing

An index that makes the query fast is the most basic explanation of an index you have heard of.

How SQL query works ?

CREATE TABLE sample_table ( 
first_name VARCHAR(32) NOT NULL,
last_name VARCHAR(32) NOT NULL,
age INT,
mobile_no VARCHAR(10)
);
DELIMITER $$
DROP PROCEDURE generate_data;
CREATE PROCEDURE generate_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000 DO
INSERT INTO `sample_table` (`first_name`,`last_name`,`age`,`mobile_no`) VALUES (
CONV(FLOOR(RAND() * 99999999999999), 10, 36),
CONV(FLOOR(RAND() * 99999999999999), 10, 36),
ROUND(RAND()*100,2),
LPAD(FLOOR(RAND() * 10000000000), 10, '0')
);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL generate_data();
show index from sample_table;
EXPLAIN SELECT * from sample_table where mobile_no='5554688121';

What is Index?

Primary Index/Key

SHOW EXTENDED INDEX FROM sample_table;
ALTER TABLE sample_table ADD PRIMARY KEY (mobile_no);
CREATE INDEX mobile_no_idx ON sample_table(mobile_no);
SHOW INDEX FROM sample_table;
EXPLAIN SELECT * from sample_table where mobile_no='5554688121';

Index Catagories

B-Tree Indexes

CREATE INDEX index_name ON table_name(column_name)
or
CREATE INDEX index_name ON table_name(column_name) USING BTREE;
SELECT * from table_name where column_name <= 'value';
SELECT * from table_name where column_name BETWEEN 1 AND 100;
SELECT * from table_name where column_name LIKE '%value%';

R-Tree Indexes

CREATE SPATIAL INDEX index_name ON table_name(column_name);

Hash Indexes

CREATE INDEX index_name ON table_name(column_name) USING HASH;

Covering Indexes

SELECT col1, col2 FROM table_name WHERE col3 = 'value';

Clustered Indexes

Composite Indexes

CREATE INDEX index_name ON demo_table(col1,col2,col3);
SELECT * FROM table_name WHERE col1 = 'value1' AND col2 = 'val2';

Prefix Indexes

CREATE INDEX index_name ON table_name (column_name(length));

Antipattern: Using Indexes Without a Plan

No Indexes

Too Many Indexes

When No Index Can Help

CREATE INDEX telephone_idx on sample_table(last_name, first_name);
SELECT * from sample_table ORDER BY first_name, last_name;
SELECT * FROM sample_table WHERE last_name = 'Charles' OR first_name = 'Charles' ;
EXPLAIN SELECT * from sample_table ORDER BY first_name, last_name;
EXPLAIN SELECT * FROM sample_table WHERE last_name = 'Charles' OR first_name = 'Charles' ;

References

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store