Optimize query help.

Dear all, =0AI have a query that takes a rather long time and was wond=
ering if there is =0Aanyway to optimize it.=0ANormally we removing duplicat=
e records by phone number. This query takes about a =0Asecond and it =
really slows down the process when we are importing several 1000 records a =
=0Aday. Here is the query: SELECT count(id) c FROM leads=0AWHERE=
(phone_home =3D '(770) 512-8990' =0AOR phone_work =3D '(770) 512-8990' =0A=
OR phone_other =3D '(770) 512-8990' =
=0AOR phone_mobile =3D '(770) 512-8990' =0AOR phone_fax =3D '(770) 512-89=
90') =0AAND date_entered >DATE_SUB(N=
OW(),INTERVAL 45 DAY) =0AAND deleted !=3D '1'; This is the describe:=
DESCRIBE SELECT count( id ) c=0AFROM leads=0AWHERE (=0Aphone_home =3D=
'(770) 512-8990'=0AOR phone_work =3D '(770) 512-8990'=0AOR phone_other =3D=
'(770) 512-8990'=0AOR phone_mobile =3D '(770) 512-8990'=0AOR phone_fax =3D=
'(770) 512-8990')=0AAND date_entered > DATE_SUB( NOW( ) , INTERVAL 45 =0AD=
AY ) =0AAND deleted !=3D '1' =0Aid =0Aselect_type =0Atable =0Atype =0A=
possible_keys =0Akey =0Akey_len =0Aref =0Arows =0AExtra 1 SIMPLE lead=
s ALL =0Aidx_del_user,phone_home,phone_mobile,phone_work,phone_oth er,phone_=
fax,date_entered=0A NULL NULL NULL 636433 Using where Any thoughts?=
Thank you, Paul =0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Optimize query help.

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 =3D 'xx'
UNION
select id from leads where phone_home =3D 'xx'
UNION
-- etc..
) tmp

- michael dykman

On Tue, Mar 15, 2011 at 1:15 PM, Paul Nowosielski
wrote:
> Dear all,
>
>
> I have a query that takes a rather long time and was wondering if there i=
s
> 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 recor=
ds a
> day.
>
> Here is the query:
>
> SELECT count(id) c =A0FROM leads
> WHERE (phone_home =3D '(770) 512-8990'
> OR phone_work =3D '(770) 512-8990'
> OR phone_other =3D '(770) 512-8990'
> OR phone_mobile =3D '(770) 512-8990'
> OR phone_fax =3D '(770) 512-8990')
> AND date_entered >DATE_SUB(NOW(),INTERVAL 45 DAY)
> AND deleted !=3D '1';
>
> This is the describe:
>
> DESCRIBE SELECT count( id ) c
> FROM leads
> WHERE (
> phone_home =3D '(770) 512-8990'
> OR phone_work =3D '(770) 512-8990'
> OR phone_other =3D '(770) 512-8990'
> OR phone_mobile =3D '(770) 512-8990'
> OR phone_fax =3D '(770) 512-8990')
> AND date_entered > DATE_SUB( NOW( ) , INTERVAL 45
> DAY )
> AND deleted !=3D '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,dat=
e_entered
> =A0NULL NULL NULL 636433 Using where
>
> Any thoughts?
>
> Thank you,
>
> Paul
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May 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=3Dgcdmg-mysql-2@m.gmane.o rg