List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:March 15 2011 5:22pm
Subject:Re: Optimize query help.
View as plain text  
The OR conditions require a full table scan everytime this is called.
You didn't say how many rows you had, nor if there were indexes on
your various phone_xxx fields.  If you do, you should get some value
by approaching it as a UNION

select count(id)from (
   select id from leads where phone_work = 'xx'
UNION
   select id from leads where phone_home = 'xx'
UNION
 -- etc..
) tmp

 - michael dykman

On Tue, Mar 15, 2011 at 1:15 PM, Paul Nowosielski
<paulnowosielski@stripped> wrote:
> Dear all,
>
>
> I have a query that takes a rather long time and was wondering if there is
> anyway to optimize it.
> Normally we removing duplicate records by phone number. This query takes about a
> second and
>
> it really slows down the process when we are importing several 1000 records a
> day.
>
> Here is the query:
>
> SELECT count(id) c  FROM leads
> WHERE (phone_home = '(770) 512-8990'
> OR phone_work = '(770) 512-8990'
> OR phone_other = '(770) 512-8990'
> OR phone_mobile = '(770) 512-8990'
> OR phone_fax = '(770) 512-8990')
> AND date_entered >DATE_SUB(NOW(),INTERVAL 45 DAY)
> AND deleted != '1';
>
> This is the describe:
>
> DESCRIBE SELECT count( id ) c
> FROM leads
> WHERE (
> phone_home = '(770) 512-8990'
> OR phone_work = '(770) 512-8990'
> OR phone_other = '(770) 512-8990'
> OR phone_mobile = '(770) 512-8990'
> OR phone_fax = '(770) 512-8990')
> AND date_entered > DATE_SUB( NOW( ) , INTERVAL 45
> DAY )
> AND deleted != '1'
>
>
> id
> select_type
> table
> type
> possible_keys
> key
> key_len
> ref
> rows
> Extra
>
> 1 SIMPLE leads ALL
> idx_del_user,phone_home,phone_mobile,phone_work,phone_other,phone_fax,date_entered
>  NULL NULL NULL 636433 Using where
>
> Any thoughts?
>
> Thank you,
>
> Paul
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
Thread
Optimize query help.Paul Nowosielski15 Mar
  • Re: Optimize query help.Michael Dykman15 Mar