Indexes, the optimizer, and doing efficent selects

Basics

Btree-Indexes must be less then 1024 bytes, so if you have a utf8 column that is 255 characters mySQL assumes 728 bytes, if you combine that with other varchar(255) utf8 columns you'll get an error.


Compound, composite indexes:

This term is used allot. It basically means 1 index across multiple columns.

For instance you have a table with

A tinyint
B tinyint
C tinyint
D tinyint

and a index on A,B,C taking 3 bytes.

following the principles of left-most-prefix key lookups (assume AND between columns)

A,B,C is an index lookup using 3 bytes
A,B order by C is a index Lookup taking 3 bytes 1 is used for a efficent sort.
A,B is an index lookup using 2 bytes
A is an index lookup using 1 byte

A, C is an index lookup using 1 byte. Notice that A,C does not use 2 bytes of the index making the lookup scan the result set of A, since A,C doesn't follow left-most in the compound index C comes after B ;))


B,C is not a index lookup causing a table scan.

A order by D is a index lookup using 1 byte with a filesort sorting the result set after the 2nd pass of finding the results.


Above we assumed that all the index lookups where based off of an 'AND' Clause. OR sucks in mySQL

A OR B uses a 1 byte index lookup scaning all of B making the query very slow.

How do we get around this?

USE UNION

SELECT * FROM ABCD_TABLE WHERE A=1 UNION SELECT * FROM ABCD_TABLE WHERE B=1

assuming that another index exists on B in the leftmost prefix of the compound index.

This is very fast.


More to come