472,971 Members | 2,157 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Optimize a MySQL OR query

I have read a book, which suggests we should change OR to UNION for better
performance, but currently I have too many OR clauses(I have a query with
100 ORs) and it does not sound good to have 100 UNION.
Any suggestion?
Will it help if I create an index for (SetID, PID)?
SELECT FileID, PID FROM File WHERE SetID = 4 AND
(PID = \path\subpath\morepath\0107.html OR
PID = \path\subpath\morepath\0101.html OR
PID = \path\subpath\morepath\0103.html OR
PID = \path\subpath\morepath\0105.html OR
PID = \path\subpath\morepath\0106.html OR
PID = \path\subpath\morepath\0102.html OR
PID = \path\subpath\morepath\0117.html OR
PID = \path\subpath\morepath\0127.html OR
PID = \path\subpath\morepath\0137.html OR
PID = \path\subpath\morepath\0147.html);
Jul 20 '05 #1
4 2385
"jy2003" <jy****@sbcglobal.net> wrote in message news:Qmxvc.78247
I have read a book, which suggests we should change OR to UNION for better
performance, but currently I have too many OR clauses(I have a query with
100 ORs) and it does not sound good to have 100 UNION.
Any suggestion?
Will it help if I create an index for (SetID, PID)?
SELECT FileID, PID FROM File WHERE SetID = 4 AND
(PID = \path\subpath\morepath\0107.html OR
PID = \path\subpath\morepath\0101.html OR
PID = \path\subpath\morepath\0103.html OR
PID = \path\subpath\morepath\0105.html OR
PID = \path\subpath\morepath\0106.html OR
PID = \path\subpath\morepath\0102.html OR
PID = \path\subpath\morepath\0117.html OR
PID = \path\subpath\morepath\0127.html OR
PID = \path\subpath\morepath\0137.html OR
PID = \path\subpath\morepath\0147.html);


See my reply to the recent thread "Indexes with OR queries". Basically the
solutions are to use an ORDER BY, or MySql extension, as described fully in
that post. There's actually a third solution: create a temporary table (and
MySql has an extension to create temporary tables) and insert the 10 PID
values above and then create a two table join.
Jul 20 '05 #2
> See my reply to the recent thread "Indexes with OR queries". Basically
the
solutions are to use an ORDER BY, or MySql extension, as described fully in that post. There's actually a third solution: create a temporary table (and MySql has an extension to create temporary tables) and insert the 10 PID
values above and then create a two table join.


I can't find the thread "Indexes with OR queries", but I will try with your
solutions. Thanks.
Jul 20 '05 #3
"jy2003" <jy****@sbcglobal.net> wrote in message news:Eqzvc.4307
See my reply to the recent thread "Indexes with OR queries". Basically
I can't find the thread "Indexes with OR queries", but I will try with your solutions. Thanks.
Search on http://groups.google.com.

If you type

Indexes with OR queries

in the search box you get strange results because OR is a keyword for google
searches.

Try this in the searchbox

group:mailing.database.mysql insubject:"Indexes with OR queries"
author:siemel


"Jon" <jo**********@yahoo.co.uk> wrote in message
The query is select date, total from actions where member_id="xxx" or
member_id="yyy" (reason for the OR statement is because a single
members account can be linked to a second one, so we have to use OR to
find the results for all the ids attached to the one account). Index
is on member_id.
Lots of databases have problems with OR queries. You can use member_id in
('xxx', 'yyy') but the database is usually still confused, though this
syntax is nicer to read.

Note the usual trick, portable because it is ANSI SQL, is to use an order by
clause. But MySql still doesn't pick the right index.

You can use the use index(index_list), or force index, or ignore index
extensions.

select date, total
from actions use index (member_index_name)
where member_id="xxx" or member_id="yyy"

With force index MySql will pick the right index, but your version of MySql
probably doesn't support this keyword. Worth a shot.
(reason for the OR statement is because a single
members account can be linked to a second one, so we have to use OR to
find the results for all the ids attached to the one account). Index
is on member_id.
Maybe you should revisit the design. You can have a linking table that
links one user to another. Then suppose user A logs in. Then

select *
from
user U1
inner join linkusers on U1.id = linkusers.user1_id
inner join user U2 on linkusers.user2_id = U2.id
inner join actions on U2.id = actions.member_id
where
U1.login = 'xyz'
order by
U1.login

Table user has index or primary key (id).
Table linkusers has index (user1_id) or (user1_id, user2_id).
Table actions has index (member_id).
I am using MySQL 3.23.58.


Jul 20 '05 #4
> Search on http://groups.google.com.

If you type

Indexes with OR queries

in the search box you get strange results because OR is a keyword for google searches.

Try this in the searchbox

group:mailing.database.mysql insubject:"Indexes with OR queries"
author:siemel


I have never known that you can use Google that way, greate thanks!
Jul 20 '05 #5

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

Similar topics

0
by: Andreas Falck | last post by:
Hi, I ran the code below on two different versions, 4.0.14 and 4.0.16 respectively, both running RH 7.3 on intel. In version mysql server version 4.0.14 the SELECT privelege suffices for...
6
by: Bruce D | last post by:
Could someone please help to explain why the following query isn't using the index... explain select id from kbm where state = 'MA' table type possible_keys key key_len ref rows Extra...
4
by: Huaer.XC | last post by:
>From the following MySQL command: EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON t3.name = t1.name WHERE t1.id IN(123, 124); which result is:...
2
by: kshirsagar007 | last post by:
friends, I would like to optimize the following query....as its taking 2 minutes to get the records. select a.CODE "Code", ud.Name "Name", a.SchemeId "SchemeID" from (MASTER sh,...
5
chathura86
by: chathura86 | last post by:
SELECT MINIMUMPAYMENT.CARDNO, CARD.NAMEONCARD, MINIMUMPAYMENT.MINPAYMENTCOUNT, CARD.CREDITLIMIT, LASTSTATEMENTSUMMARY.MINAMOUNT AS MIN_PYM, CARDTYPE.NAME FROM
2
by: bravo | last post by:
hi everyone , i am trying to join two tables , the query is working fine when the records in the tables are less but when the records are in huge numbers say 1-2 million then the system hangs... i am...
11
by: bravo | last post by:
hi i need to optimize the join query, which joins three tables say table1 ,table2 , table3 each having huge volume of records... the query is as select table1.id,table2.time,table3.Status from...
1
by: acornejo | last post by:
Hi All I've the following code I need to optimize. Currently tblOutgoing is about 250K registers and growing at a rate of about 20k records per day. This code takes me over 5 secs to run on each...
3
zabsmarty
by: zabsmarty | last post by:
Can any one help me to make my query code optimize and load faster. Please help me or any example what steps should we use to optimize. Thank You
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.