maria-developers team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

hi oleksandr!
i understood some points... but let me explain better...
about optimizer part, i was talking about constant propagation and others
optimizations, like this:

1) "select .. where 0=0 and a=1 and a=2"
optimized to:
"0=0 and a=1 and a=2" => "true and false" => "always false" (ok here
executing is fast but just an example of optimization)

2) "select ... where a in (1,2,3,4) and a>2 AND a <4"
optimized to
"select ... where a=3"

with only the rewrite of parsed query (without optimization), we have only
a gain of rewrite "FrOm" to "FROM", remove white spaces and others simple
"normalizations", that's not a good gain (and consume cpu/memory)
since we need to optimize the query (to execute it) that's not a new load
to mysql/mariadb
-----

going back to my idea...

*(A)*
the 'new' load in this case is
1) time to write 'normalized' query (after optimization)
   cpu consume + memory writing the normalized query + increased lock time
for myisam table locks and others locks
2) check 'normalized' query cache (after normalized query write)
   cpu to find query in query cache + a query cache lock overload +
increased lock time + memory to save normalized queries
another important overload that i don't see?

for (1) we can get time used by explain extended to know how many time it
take?
-----

*(B)*
talking about implementation... i was thinking something like:

1)check if we should do normalized query cache hit, considering raw query
length, number of tables, number of comparations, maybe others prunes
   this avoid aditional load of a normalized query write, the main problem
of this kind of implementation, right?
2)add a write function after optimization to write the normalized query
3)check normalized query cache
3.1) link current raw query to normalized query cache
3.2) return result to client
3.3) end the current query parse (remove locks, etc)
4) when not found in normalized query cache, continue normal query
parse/execution
5) link the raw query to normalized query cache

is my "high level" implementation simple like i think, or the execution is
a bit more complicated?
since i'm not a guru of mysql/maria source code, there's some locks and
others flows that a "begineer coder" (me) don't see?

-----

*(C)*
i read some (3 or 4) books about mysql internals, but they don't tell how
to change/understand the current optimizer, just how to implement a new
optimizer/parser :/
all i know about mysql optimizations is reading mail list and source code
and running debugger
do you know some book that talk about internal optimizer?

well, thanks again for your time! :)