List:Internals« Previous MessageNext Message »
From:Paul Cadach Date:August 20 2001 12:08pm
Subject:Missed optimization in 3.23.33
View as plain text  
Hi,

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

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


WBR,
Paul.


Attachment: [application/x-pkcs7-signature] smime.p7s
Attachment: [application/x-pkcs7-signature] smime.p7s
Thread
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