efficiency using or within an sql statement on mysql

[Fred]

Hi all,


I have done a lot of experimentation s using several
"or" predicates within an sql select statements on mysql.
My only conclusion so far is that whatever syntax or order
I use it is always slower than doing seperate
sql statements (one for each or) and programaticaly
adding them to a vector.

The performance of any "or" statement is really really bad.

does anyone out there manages to get resonable results out of
an or in an sql statements? If you do can you point me to what
rules you use to get the best out of mysql.



here is a typical example of the or I have used

select
customers.accou nt_nb, customers.name, addresses.telep hone_1,
addresses.telep hone_2, addresses.telep hone_3, addresses.post_ code
from customers, addresses
where (
(addresses.tele phone_1 = '+44 (0) 122 464 264 7') or
(addresses.tele phone_2 = '+44 (0) 122 464 264 7') or
(addresses.tele phone_3 = '+44 (0) 122 464 264 7');


Thanks for your help
Regards,
Fred

[Bill Karwin]

Re: efficiency using or within an sql statement on mysql

Fred wrote:[color=blue]
> The performance of any "or" statement is really really bad.
> ...where (
> (addresses.tele phone_1 = '+44 (0) 122 464 264 7') or
> (addresses.tele phone_2 = '+44 (0) 122 464 264 7') or
> (addresses.tele phone_3 = '+44 (0) 122 464 264 7');[/color]

Have you tried using the EXPLAIN statement to get an idea of which
indexes are being used in your queries, whether using OR or querying
each field individually?
See http://dev.mysql.com/doc/mysql/en/explain.html

Do you have indexes on all three telephone fields? It seems like you
should.

Have you read the chapter of the MySQL doc on performance optimization?
It is worthwhile reading for anyone using MySQL.

http://dev.mysql.com/doc/mysql/en/mysql-optimization.html

Regards,
Bill K.

[Fred]

Re: efficiency using or within an sql statement on mysql

Thanks for your time Bill,

I will look at both those sugestions.
regards,
Fred

[Fred]

Re: efficiency using or within an sql statement on mysql

Thanks for your time Bill,

I will look at both those sugestions.
regards,
Fred

[Fred]

Re: efficiency using or within an sql statement on mysql

ok,

I have spent a few more hours here are my result;


this is the basic query (method 1) I want to execute

---------- start -----------
select customers.accou nt_nb, customers.name, addresses.telep hone_1,
addresses.telep hone_2, addresses.telep hone_3, addresses.post_ code
from customers, addresses
where (
(addresses.tele phone_1 = '+44 (0) 122 464 264 7') or
(addresses.tele phone_2 = '+44 (0) 122 464 264 7') or
(addresses.tele phone_3 = '+44 (0) 122 464 264 7') or
( (addresses.addr ess_1 like '11 West Street%') and
(addresses.post _code = 'EX13 5NU') ) or

( (addresses.addr ess_1 like '11 West Street%') and (customers.name
like 'Aberdeen') )
)
and addresses.id = customers.addre ss_id ;
----------- end ----------

which I also split in 5 statement one coresponding to each or
statement
(method 2) as folow



-------------- start ----------
select customers.accou nt_nb, customers.name, addresses.telep hone_1,
addresses.telep hone_2, addresses.telep hone_3, addresses.post_ code from
customers, addresses where (addresses.tele phone_1 = '+44 (0) 122 464
264 7') and addresses.id = customers.addre ss_id ;

select customers.accou nt_nb, customers.name, addresses.telep hone_1,
addresses.telep hone_2, addresses.telep hone_3, addresses.post_ code from
customers, addresses where (addresses.tele phone_2 = '+44 (0) 122 464
264 7') and addresses.id = customers.addre ss_id ;

select customers.accou nt_nb, customers.name, addresses.telep hone_1,
addresses.telep hone_2, addresses.telep hone_3, addresses.post_ code from
customers, addresses where (addresses.tele phone_3 = '+44 (0) 122 464
264 7') and addresses.id = customers.addre ss_id ;

select customers.accou nt_nb, customers.name, addresses.telep hone_1,
addresses.telep hone_2, addresses.telep hone_3, addresses.post_ code from
customers, addresses where ( (addresses.addr ess_1 like '11 West
Street%') and (addresses.post _code = 'EX13 5NU') ) and addresses.id =
customers.addre ss_id ;

select customers.accou nt_nb, customers.name, addresses.telep hone_1,
addresses.telep hone_2, addresses.telep hone_3, addresses.post_ code from
customers, addresses where ( (addresses.addr ess_1 like '11 West
Street%') and (customers.name like 'Aberdeen') ) and addresses.id =
customers.addre ss_id ;

---------------end ------------

method 1 took 3.00s
method 2 took 9.74s


Then I created indexes
first on telephone_1

method 1 took 2.73s
method 2 took 7.40s


Then I created an indexe
on telephone_2

method 1 took 2.73s
method 2 took 5.00s

Then I created an indexe
on telephone_3

method 1 took 2.73s
method 2 took 3.03s

Then I created an indexe
on address_1

method 1 took 2.73s
method 2 took 0.75s


In this case as this statement is one of the most used by users
I have once again choseen to use the 5 statements split and
converge them programaticaly. ..

I still don't get why the use of or is much slower (even with
every search field indexed )than the sum of the diferent select
making it.






telephone_1, 2, and 3
address_1,
post_code
name

[Bill Karwin]

Re: efficiency using or within an sql statement on mysql

Fred wrote:[color=blue]
> I still don't get why the use of or is much slower (even with
> every search field indexed )than the sum of the diferent select
> making it.[/color]

I suggest you use the EXPLAIN statement to see what indexes are being
exercised in both types of queries. MySQL can disregard some of the
indexes if it believes they won't help.

Regards,
Bill K.