I've been studying indexes and can't completely understand how MYSQL is capable of using indexes at all for statements like
IN() AND IN() ... AND IN()
The book I am reading suggests that when we have an index (a, b, ...) but user wants to search for b and we have a low cardinality in a, we can use a trick and simply add IN()
WHERE a IN ('x1', 'x2', ... 'all possible values go here') AND `b`>123
Let's say we have the following data
x1 1
x1 4
x1 456
x2 5
x3 1
x3 2
x3 3
x4 1234
How can it traverse this tree to satisfy the query above? Will it simply create all possible combinations for IN-s and will pretty much traverse tree for each of these queries?
WHERE `a`='x1' AND `b`>123
WHERE `a`='x2' AND `b`>123
...
therefore making this trick of limited usefulness because as number of IN-s increases, the number of all possible IN combinations increases dramatically and we'll have to run through the B-tree for each of these combinations? If the above is true, does this mean that there is some theoretical point where tricking an index with IN will get slower than not using index at all?
It merges the results from several passes. So yes, your assumption is correct. :)
See http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html
Before index merges, mysql could not satisfy such queries from an index.