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); 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.
> 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.
"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.
> 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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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:...
|
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,...
|
by: chathura86 |
last post by:
SELECT
MINIMUMPAYMENT.CARDNO,
CARD.NAMEONCARD,
MINIMUMPAYMENT.MINPAYMENTCOUNT,
CARD.CREDITLIMIT,
LASTSTATEMENTSUMMARY.MINAMOUNT AS MIN_PYM,
CARDTYPE.NAME
FROM
|
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...
|
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...
|
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...
|
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
|
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=()=>{
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
| |