Cardinality and selectivity for indexes.

selectivity = cardinality / count(*)

cardinality is the number of distinct values.

a column with higher selectivity is preferred for the index.

primary has a selectivity of 1.

Composite index

a single on multiple indexes.

sequence of columns defined in the index is very important.

1. start with left and then go towards right, no skipping.
2. stops at the first range.

Functional Indexes

when indexes are obfuscated, they can’t be used

alter table person add index birthday (birthday);

select * from person where birthday = ‘1994-01-01’;
//This uses an index.
select * from person where month(birthday) = 2;
//This does not use an index.

alter table person add index month_birthday(month(birthday));

above example shows how to create functional index.