You didn't say what version of MySQL you're using or if you're using MyISAM
tables (assumed).
Since you are using OR's you may find it faster to use Union on 3 select
statements.
It looks something like this:
SELECT * FROM users
JOIN sites ON users.ID = sites.userid
WHERE users.username = 'user1234'
union
SELECT * FROM users
JOIN sites ON users.ID = sites.userid
WHERE users.email = 't...@test.com'
union
SELECT * FROM users
JOIN sites ON users.ID = sites.userid
WHERE sites.email = 't...@test.com' <mailto:'ccf...@googlemail.com'>
This way each Select statement will use whatever index they like,
independent of the other Select statements.
Mike
At 07:25 AM 8/20/2009, you wrote:
Hi Everyone,
Can anyone suggest why the following query is taking upwards of 5
seconds to run?
SELECT * FROM users
JOIN sites ON users.ID = sites.userid
WHERE users.username = 'user1234'
OR users.email = 't...@test.com'
OR sites.email = 't...@test.com' <mailto:'ccf...@googlemail.com'>
The users table has an index on the username field and another on the
email field.
The sites table has an index on the email field.
Both tables contain around 200k rows.
An explain of the query shows that no indexes are being used:
id select_type table type possible_keys key key_len
ref rows Extra
1 SIMPLE users ALL PRIMARY,username,email NULL NULL
NULL 155424
1 SIMPLE sites ref userid,email userid 4
dyos.users.ID 1 Using where
Can anyone tell me how to make it use the available indexes? Or do I
need to add some sort of multi-column index? I'm guessing not as I'm
doing OR's
Thanks
Simon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
