An in-depth look at Database Indexing

Kousik Nath
Apr 16 · 20 min read
Photo by Markus Spiske on Unsplash
CREATE TABLE index_demo ( 
name VARCHAR(20) NOT NULL,
age INT,
pan_no VARCHAR(20),
phone_no VARCHAR(20)
);

How do we verify that we are using InnoDB engine?

SHOW TABLE STATUS WHERE name = 'index_demo' \G;
EXPLAIN SELECT * FROM index_demo WHERE name = 'alex';

Primary Key:

What if you don’t create any primary key yourself?

SHOW EXTENDED INDEX FROM index_demo;

What is the difference between key & index?

ALTER TABLE index_demo ADD PRIMARY KEY (phone_no);
SHOW INDEXES FROM index_demo;
EXPLAIN SELECT * FROM index_demo WHERE phone_no = '9281072002';

Clustered Index:

What does physical data organization mean?

Advantage of Clustered Index:

SELECT * FROM index_demo WHERE phone_no > '9010000000' AND phone_no < '9020000000'

Constraints of Clustered Index:

Relationship between Primary Key & Clustered Index:

Structure of Primary key (clustered) Index:

Is it possible to create a primary index on a non-primary key?

Is it possible to delete a primary key?

ALTER TABLE `index_demo` DROP PRIMARY KEY;- If the primary key does not exist, you get the following error:"ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists"

Advantages of Primary Index:

Disadvantages of Primary Index:

Secondary Index:

When do you need a Secondary Index?

How to create a secondary index in MySQL?

CREATE INDEX secondary_idx_1 ON index_demo (name);

Structure of Secondary Index:

Advantages of a Secondary Index:

Disadvantages of a Secondary Index:

Consideration before you delete a Primary Index:

UNIQUE Key Index:

CREATE UNIQUE INDEX unique_idx_1 ON index_demo (pan_no);

Composite Index:

CREATE INDEX composite_index_1 ON index_demo (phone_no, name, age);CREATE INDEX composite_index_2 ON index_demo (pan_no, name, age);

Why do we use composite indices? Why not define multiple secondary indices on the columns we are interested in?

How does composite index work?

+--------+------+------------+------------+| name   | age  | pan_no     | phone_no   |+--------+------+------------+------------+| kousik |   28 | HJKXS9086W | 9090909090

How to identify if you need a composite index:

Covering Index:

SELECT age FROM index_demo WHERE pan_no = 'HJKXS9086W' AND name = 'kousik'
EXPLAIN FORMAT=JSON SELECT age FROM index_demo WHERE pan_no = 'HJKXS9086W' AND name = '111kousik1';

Partial Index:

CREATE INDEX secondary_index_1 ON index_demo (name(4));

What happens under the hood when we define an index?

SHOW EXTENDED INDEXES FROM index_demo;

General Indexing guidelines:

References:

freeCodeCamp.org

This is no longer updated. Go to https://freecodecamp.org/news instead

Kousik Nath

Written by

Deep discussions on problem solving, distributed systems, computing concepts, real life systems designing. Developer @PayPal. https://in.linkedin.com/in/kousikn

freeCodeCamp.org

This is no longer updated. Go to https://freecodecamp.org/news instead