Hi!
>>>>> "Nicola" == Nicola Cisternino <ncister@stripped> writes:
Nicola> Hello .... and excuse me for the language !!..
Nicola> I've used C-ISAM filesystem structure for 15 years and i know very well
Nicola> "low-level" use of B-TREE access.
Nicola> Now i'm testing "MySql" capabilities with great interest and i should
Nicola> want propose you a simple technique to optimize the SELECT statement
Nicola> that i always use in my querying code.
Nicola> My question is: why the MySql Engine NOT USE index when some key segment
Nicola> of it aren't "complete" ???? ..... it's POSSIBLE !!
Nicola> I will try to explain you my idea with an example:
<cut>
Nicola> ... with this query the SQL Engine MUST USE key2 index !!! (becouse F3
Nicola> is included in this key!) and QUICKLY SKIP invalid records forcing a new
Nicola> value in F3 field and "restarting" with it ........
Nicola> The steps are:
Nicola> 1) start with a "clean" key2 (F2="blank" and F3="blank") and read
Nicola> record n. 1 (1,A,b)
Nicola> 2) force "m" value in F3 field (becouse current value "b" is less then
Nicola> "m" !) and quickly point record n.3
Nicola> 3) read next record (rec -> 4/A/t)
Nicola> 4) force an "FF" hexadecimal value in F3 field (becouse current value
Nicola> "t" is greater then "m") and quickly point record n.100
Nicola> 5) force "m" value in F3 field (becouse current value "g" is less then
Nicola> "m" !) and quickly point record n.102
Nicola> 6) read next record (rec -> 103/C/o)
Nicola> 7) force an "FF" hexadecimal value in F3 field (becouse current value
Nicola> "o" is greater then "m") and
Nicola> quickly point record n.800
Nicola> 8) force "m" value in F3 field (becouse current value "a" is less then
Nicola> "m" !) and quickly point record n.803
Nicola> 9) read next record (rec -> 804/F/n)
Nicola> 10) force an "FF" hexadecimal value in F3 field (becouse current value
Nicola> "n" is greater then "m") and
Nicola> quickly point record n.499994 ( !!!)
Nicola> 11) force "m" value in F3 field (becouse current value "d" is less then
Nicola> "m" !) and quickly point record n.499997
Nicola> 12) read next record (rec -> 499998/F/n)
Nicola> 13) force an "FF" hexadecimal value in F3 field (becouse current value
Nicola> "n" is greater then "m") and ISAM return "EOF"
Nicola> I think that this simply sequence can be generalized in a standard
Nicola> algorhythm into your SQL engine (... i have already done it in my
Nicola> standard COBOL engine .....)
Nicola> If you want i can send you a complete COBOL source example of the
Nicola> previous example ......
The problem is that it's quite hard to do this for the general case;
It's relatively easy to do if you have only searching after one
column, but in the general case it's not that easy:
WHERE F2>= 'a' and F3='m' or F3='n' and F2 <= 'b'.
To optimize the above MySQL builds a tree where F1 is unknown, but
currently it drops instances where there are unknown entities at the
start of the key. It may be easy to implement the above in the
current code to solve the most frequent examples; I have saved this
mail in our TODO folder to look at the next time we will work on the
range optimizer...
Thanks for the idea.
Regards,
Monty
