By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,827 Members | 2,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,827 IT Pros & Developers. It's quick & easy.

Indexes with OR queries

P: n/a
Jon
I wonder if anyone can help me with an answer for this, the question
seems well covered in the achieves, but a lot of the answers disagree
with each other and I couldn't find no real solution.

I have a table with 50k rows, and growing quickly, each row is quite
large. The application is a members based, and once a member logs in
it has to query this table to find all actions for that member. So
some results will be larger than others.

As the table grew I noticed it was getting quite slow, so I used the
EXPLAIN query to see what indexes it was using - MySQL finds some
possible_keys but then doesn't use them.

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.

So I started to look for information why MySQL isn't using the Index
on OR statements, and that what leads me here. So if anything can
help that would be great, another solution would be to create a new
table and create a summary of the information which would be a lot
less rows, but I would like to work out why the Index isn't working.

I am using MySQL 3.23.58.

Thank you.

Jon.
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"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 #2

This discussion thread is closed

Replies have been disabled for this discussion.