List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:February 15 2000 6:25am
Subject:...A LITTLE IDEA TO OPTIMIZE SELECT STATEMENT .....
View as plain text  
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
Thread
...A LITTLE IDEA TO OPTIMIZE SELECT STATEMENT .....Nicola Cisternino14 Feb
  • Alternative Character SetsAndrew G Milne14 Feb
    • Re: Alternative Character Setssinisa14 Feb
  • Re: ...A LITTLE IDEA TO OPTIMIZE SELECT STATEMENT .....sinisa14 Feb
  • Why does mysql create one index file for each table?Shangwu Qi14 Feb
    • Why does mysql create one index file for each table?Michael Widenius15 Feb
  • ...A LITTLE IDEA TO OPTIMIZE SELECT STATEMENT .....Michael Widenius15 Feb
  • Re: Alternative Character SetsArion16 Feb