List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:August 21 2001 12:55pm
Subject:Missed optimization in 3.23.33
View as plain text  

>>>>> "Paul" == Paul Cadach <paul@stripped> writes:

Paul> Hi,
Paul> May be this fixed now, but for information. I'm running next query:
Paul>     select count(*) from company c, c_phone p, c_division d where (((
Paul> like '%xxx%' and regexp
Paul> '^(25|.*[^0-9]+25|^25[^0-9]+.*|.*[^0-9]+25[^0-9]+.*)$') or (p.holder like
Paul> '%xxx%' and p.holder regexp
Paul> '^(25|.*[^0-9]+25|^25[^0-9]+.*|.*[^0-9]+25[^0-9]+.*)$')) and =
Paul> c.code and p.division = d.division and c.code = d.code) group by d.division
Paul> limit 2;
Paul> and its runs fast. But when I swaps regexp and like within each condition,
Paul> the one runs too slow. Slow query is:
Paul>     select count(*) from company c, c_phone p, c_division d where (((
Paul> regexp '^(25|.*[^0-9]+25|^25[^0-9]+.*|.*[^0-9]+25[^0-9]+.*)$' and
Paul> like '%xxx%') or (p.holder regexp
Paul> '^(25|.*[^0-9]+25|^25[^0-9]+.*|.*[^0-9]+25[^0-9]+.*)$' and p.holder like
Paul> '%xxx%')) and = c.code and p.division = d.division and c.code =
Paul> d.code) group by d.division limit 2;

Paul> May be this optimization requres to bring weighting for functions, to
Paul> execute simplest functions first, then (if condition is true) more complex,
Paul> and last (if condition still true) - most complex functions? This weight may
Paul> be a value of time which each function executed 10000 (for example) times.

The problem is that it's almost impossible for the optimizer to say
which function will be faster, as this will depend on the arguments
and the data.  When executing expression MySQL does most things from
left to right, so it's relatively easy for a human to get MySQL to do
the right thing.


For technical support contracts, goto
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Michael Widenius <monty@stripped>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, CTO
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland

Missed optimization in 3.23.33Paul Cadach20 Aug
  • Re: Missed optimization in 3.23.33Sinisa Milivojevic21 Aug
  • Missed optimization in 3.23.33Michael Widenius21 Aug
  • Re: Missed optimization in 3.23.33Paul Cadach21 Aug