MySQL a pouzivanie indexov pre viacnasobny range lookup.

Zaciname s uvodnou tabulkou.
Tabulka uklada vectorovu stromovu strukturu v historickych datumovych rozpatiach.
(v priemere tento ulozny sposob znizuje pocet zaznamov a velkost tabulky na 1%-5% voci ukladaniu stavu na kazdy den).


CREATE TABLE `tree` (
`tree_id` int(11) NOT NULL AUTO_INCREMENT,
`tree_type` smallint(11) NOT NULL,
`tree_vector` char(200) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`tree_depth` int(11) NOT NULL,
`date_from` date NOT NULL,
`date_to` date NOT NULL,
PRIMARY KEY (`tree_id`),
KEY `type_vector_to_from` (`tree_type`,`tree_vector`,`date_to`,`date_from`),
KEY `type_vector_from_to` (`tree_type`,`tree_vector`,`date_from`,`date_to`),
KEY `type_from_to_vector` (`tree_type`,`date_to`,`date_from`,`tree_vector`),
KEY `type_to_from_vector` (`tree_type`,`date_to`,`date_from`,`tree_vector`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


Viacero klucov je tam momentalne len preto, aby pri testoch si mohlo MySQL vybrat index, ktory je najvyhodnejsi. Po optimalizacii bude potrebny len jeden.

"Bajtove" velkosti stlpcov:
tree_type = 2B
tree_vector = 200B (pri varchare je to viac a varchar robi pri vectoroch problemy, preto char)
date_from = 3B
date_to = 3B

Spolu ma teda BinaryTree index sirku 208B.


Vytahovanie dat :
EXPLAIN SELECT *
FROM tree
WHERE 1
AND tree_type = "2220"
AND date_from <= "2010-06-01"
AND date_to > "2010-06-01"
AND tree_vector LIKE "0001%"


Vysledok :
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE mvc_tree range type_vector_to_from,type_vector_from_to,type_from_... type_to_from_vector 5 NULL 1998 Using where

(ja uz tam mam 180000 realnych zaznamov, preto uz mam vypisany pocet rows)

Pouziva LEN 5B sirku, takze len Type a To (kedze pouziva index type_to_from_vector ).


Tak skusim Forcovat index :
...
FROM tree FORCE INDEX(`type_vector_to_from`)
...

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tree range type_vector_to_from type_vector_to_from 205 NULL 12435 Using where

205 takze Type, Vector a To su pouzite (LIKE neukoncuje hladanie v BTree indexe ak je percento len na konci!)


...
FROM tree FORCE INDEX(`type_vector_from_to`)
...

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tree range type_vector_from_to type_vector_from_to 205 NULL 11598 Using where

205 takze Type, Vector a From su pouzite


...
FROM tree FORCE INDEX(`type_from_to_vector`)
...

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tree range type_from_to_vector type_from_to_vector 5 NULL 1998 Using where

5 takze Type, From su pouzite


...
FROM tree FORCE INDEX(`type_to_from_vector`)
...

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tree range type_to_from_vector type_to_from_vector 5 NULL 1998 Using where

5 takze Type, To su pouzite



Analyza ukazuje, ze mu vadi opacny Range na dvoch polickach (napriek tomu, ze MySQL manual pise, ze po Range Lookup pokracuje hladanie v indexe).

Preto orezem datumy z oboch stran :

EXPLAIN SELECT *
FROM tree
WHERE 1

AND tree_type = "2220"
AND date_from <= "2010-06-01"
AND date_from >= "0000-00-00"
AND date_to > "2010-06-01"
AND date_to <= "9999-12-31"
AND tree_vector LIKE "0001%"


id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tree range type_vector_to_from,type_vector_from_to,type_from_... type_from_to_vector 208 NULL 1934 Using where


Pouziva sa KOMPLETNY index.



Vysledna rychlost:

SELECT SQL_NO_CACHE *
FROM tree
WHERE 1

AND tree_type = "2220"
AND date_from <= "2010-06-01"
AND date_from >= "0000-00-00"
AND date_to > "2010-06-01"
AND date_to <= "9999-12-31"
AND tree_vector LIKE "0001%"

Ukázať záznamy 0 - 29 (2 987 celkovo, Dopyt zabral 0.0020 sek.)



Have fun
(kamarat nazyva MySQL - kalkulacka, ale prax mi ukazuje, ze ked sa programator nauci optimalizovat query pre mysql, je z toho naozaj vykonny nastroj).