efficiency using or within an sql statement on mysql

Thanks for your time Bill,

I will look at both those sugestions.
regards,
Fred
 >> Stay informed about: 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.account_nb, customers.name, addresses.telephone_1,
addresses.telephone_2, addresses.telephone_3, addresses.post_code
from customers, addresses
where (
(addresses.telephone_1 = '+44 (0) 122 464 264 7') or
(addresses.telephone_2 = '+44 (0) 122 464 264 7') or
(addresses.telephone_3 = '+44 (0) 122 464 264 7') or
( (addresses.address_1 like '11 West Street%') and
(addresses.post_code = 'EX13 5NU') ) or

( (addresses.address_1 like '11 West Street%') and (customers.name
like 'Aberdeen') )
)
and addresses.id = customers.address_id ;
----------- end ----------

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



-------------- start ----------
select customers.account_nb, customers.name, addresses.telephone_1,
addresses.telephone_2, addresses.telephone_3, addresses.post_code from
customers, addresses where (addresses.telephone_1 = '+44 (0) 122 464
264 7') and addresses.id = customers.address_id ;

select customers.account_nb, customers.name, addresses.telephone_1,
addresses.telephone_2, addresses.telephone_3, addresses.post_code from
customers, addresses where (addresses.telephone_2 = '+44 (0) 122 464
264 7') and addresses.id = customers.address_id ;

select customers.account_nb, customers.name, addresses.telephone_1,
addresses.telephone_2, addresses.telephone_3, addresses.post_code from
customers, addresses where (addresses.telephone_3 = '+44 (0) 122 464
264 7') and addresses.id = customers.address_id ;

select customers.account_nb, customers.name, addresses.telephone_1,
addresses.telephone_2, addresses.telephone_3, addresses.post_code from
customers, addresses where ( (addresses.address_1 like '11 West
Street%') and (addresses.post_code = 'EX13 5NU') ) and addresses.id =
customers.address_id ;

select customers.account_nb, customers.name, addresses.telephone_1,
addresses.telephone_2, addresses.telephone_3, addresses.post_code from
customers, addresses where ( (addresses.address_1 like '11 West
Street%') and (customers.name like 'Aberdeen') ) and addresses.id =
customers.address_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
 >> Stay informed about: efficiency using or within an sql statement on mysql 

Fred wrote:
 > 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.

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.
 >> Stay informed about: efficiency using or within an sql statement on mysql