Re: Is this an inefficient query?

[Giorgos Gaganis]

Jay Blanchard wrote:
[snip]

$qin = "SELECT COUNT(cdr.ani) AS qtyin, SUM(cdr.calldur/60) AS minin ";
$qin .= "FROM tblCDR cdr LEFT OUTER JOIN CDRFilter e ";
$qin .= "ON cdr.dialedno = e.phoneNumber ";
$qin .= "WHERE e.phoneNumber IS NULL ";
$qin .= "AND (cdr.dialedno LIKE '800%' ";
$qin .= "OR cdr.dialedno LIKE '866%' ";
$qin .= "OR cdr.dialedno LIKE '877%' ";
$qin .= "OR cdr.dialedno LIKE '888%') ";
$qin .= "AND cdr.pindigs = '' ";
$qin .= "AND cdr.predig <> '2' ";
$qin .= "AND cdr.predig <> '3' ";
$qin .= "AND cdr.discn_dt <= '" . $cdrdate . "' ";
$qin .= "AND cdr.billed_flag = '' ";
[/snip]
OK, I am getting closer to the answer. It seems that the inefficiency is
born in the OR statements. Anyone have any experience with "cleaning up"
something like this?
Thanks!

Jay

Jay

An idea I have is to replace the string operations that you have with 
numeric ones. In general, numeric operations run faster than string 
operations. So I would try to replace :
> (cdr.dialedno LIKE '800%' ";
> $qin .= "OR cdr.dialedno LIKE '866%' ";
> $qin .= "OR cdr.dialedno LIKE '877%' ";
> $qin .= "OR cdr.dialedno LIKE '888%')

whith something like :
floor(cdr.dialedno/1000) in (866,877,888)
where you have to substitute 1000000 with the power of ten that gives 
you the first three digits of your phone number.

I don't know if this is going to be faster in mysql but it worked for me 
in the past for a cpp problem.

Also if it turns out to be more effient and you use it you will have to 
be careful about the fact that when you use numbers you kill the leading 
zeros. So this expr would return true both for
866123
and
00866123
contrary tou yours above expr

Giorgos



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]