Optimize a MySQL OR query

[jy2003]

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);

[Siemel Naran]

"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.

[jy2003]

> 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.

[Siemel Naran]

"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.

[jy2003]

> 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!