Indexing

The most important thing but often forgotten is INDEXING.

Why it's important?

Imagine your table has 1.000.000 records. MySQL block can contain 5 records, so you have 200.000 blocks in storage system.

When you select a row in table, it means you need to find an record in 200.000 blocks.

If each accessing to block take 0.001s and your wanted record in the block 200.000th, so it will take 0.001 x 200.000 = 200s <=> 3 minutes. Quite slow.

(Actually it's not straight forward like this formula because file-system use sequential access when scan all table, so it save I/O time then total time in fact is less than 3 minutes).

But if we have an index in this table:

• There is 200.000 blocks to hold all records , too.
• We all know MySQL store index in B-tree with the node is index value and the leaf is reference to block.
• So if we want to find an record with index we can reduce the number of blocks will be scan.
• The average block when search in B-tree is log base 2 of 200.000 ~= 17 blocks.
• The result is we only need access to 17 block to find the records we want.
• If accessing to block take 0.001s so the total time we need is 0.001 x 17 = 0.017s. Quite fast.

INDEXING

It's possible to create many index in table but we all know index is store in B-tree structure and MySQL when process query will optimize to choose which the best index to use. So, the more index in your table, the more space need to be allocate to store index , the more time to optimize.

Consider indexing in which column:

• Make index in columns that frequently used in WHERE CLAUSE
• If column's value range is small, should not make index. Example : if you have column money in your table 'income', but almost all your records has money = 0 (or other value), should not make index in money column.
• If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
• If you have index over two column (col1, col2). So query : "where col2 =x" , "where col1 = x or col2=y" will not use index.
• Create index by statement : KEY `name` (`col_name`, .......)