facebook twitter youtube
Replication Query Optimization DB Tuning Server Load and Query Locking Linux Download Interview Questions Basic DB Commands Mysql
in Mysql - 19 Feb, 2014
by Zafar Malik - no comments
Index usage methodology (Mysql B-Tree)

Below are some facts related with B-TREE index usage by mysql and one example to understand this logic.

a) If any table has approx. 75% same data then index will not be used instead mysql will do table scan.

b) Normally mysql use only single index per table.

c) Index ordering methodology: Mysql will use index as per their order.

For example there is an combined index on a, b and c field idx_a_b_c(a,b,c)

i. select a, b, c from mytable where a=4

This query will use index as ‘a’ column is first in index order.

ii. select a, b, c from mytable where a=4 and b=5

This query will use combined index on a & b as these column are continue in index order.

iii. select a, b, c from mytable where a=4 and b=5 and c >= “2011-01-01 00:00:00″

This query will use combined index on a, b & c as these column are continue in index order.

iv. select a, b, c from mytable where c >= “2011-01-01 00:00:00″

This query will not use index as mysql consider index from left most corner and column c is not a left most column in index.

v. select a, b, c from mytable where a=4 and c >= “2011-01-01 00:00:00″ and c < “2011-01-02 00:00:00″

This query will use only index on ‘a’ column but not of ‘c’ column as continuity is breaking here from left side. So this query will use index on a column and then scan table for column c for corresponding rows as per filter on column a.

Leave a Reply