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 <paulnowosiel...@yahoo.com> 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=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
