473,322 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

efficiency using or within an sql statement on mysql

Hi all,
I have done a lot of experimentations 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.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');
Thanks for your help
Regards,
Fred
Jul 23 '05 #1
5 1603
Fred wrote:
The performance of any "or" statement is really really bad.
...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');


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/my...imization.html

Regards,
Bill K.
Jul 23 '05 #2
Thanks for your time Bill,

I will look at both those sugestions.
regards,
Fred
Jul 23 '05 #3
Thanks for your time Bill,

I will look at both those sugestions.
regards,
Fred
Jul 23 '05 #4
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
Jul 23 '05 #5
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.
Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Daan | last post by:
Hello all, I have attempted to write my own forum using PHP and MySQL. It works, but the efficiency is far from ideal. It takes a long time to load e.g. the list with threads in a certain forum....
2
by: Jon Dellaria | last post by:
I have been using MySql as the database using JSP's and JavaBeans but recently I have wanted to start using the database connection pooling mechanism built into TomCat. I think I am having a...
2
by: Oliver Corona | last post by:
I am wondering if anyone has any insights on the performance benefit (or detriment) of declaring local variables instead of referencing members. Is allocating memory for a new variable more...
3
by: zaphod | last post by:
I want to use MS Accesss as a front end for an existing MySQL database. Since Acceess doesn't have the same data types, eg. ENUM, am I tied to the smaller subset of types supported by Access? I'm...
4
by: Japhy | last post by:
Hello, I'm am pulling data from a mysql db and want to use the data to populate a <ul. Here are relavent parts of my code : $wohdate = mysql_result($wohRS,$wohndx,woh_date); $woh_display...
3
by: -Michelle- | last post by:
Hi Using A2003 on XP I am wondering from the MVP's and others, what is the most efficient way (in terms of time to process) of updating data in a table, using the docmd.RunSQL or Recordset ...
2
by: bballr | last post by:
I have a problem that I'm not sure if it can be done. I'm trying to use the MySQL C API to be able to use a normal sql insert statement that will send the data or file to a directory and NOT the...
4
by: bizt | last post by:
Hi, I want to find an easy way to select only rows within a certain range when I do a select. Im sure I have seen this done with a SELECT statement but cant remember if or how it was done. Can...
2
by: Taras_96 | last post by:
Hi everyone, I'm trying to run a number of commands stored within a sql file from within php using mysqli::query. The syntax I'm using is: source C:\data\projects\forum...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.