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