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
MySQL can only use one index at a time. Your OR's makes it unable to use any
of the indexes.
You could try a UNION:
SELECT * FROM users
WHERE users.username='user1234'
UNION
SELECT * FROM users
INNER JOIN sites ON users.id=sites.userid
WHERE users.email='t...@test.com'
UNION
SELECT * FROM users
On Thu, Aug 20, 2009 at 10:43 AM, Johnny Withersjoh...@pixelated.net wrote:
MySQL can only use one index at a time.
That was fixed years ago, in MySQL 5.0.
http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html
- Perrin
--
MySQL General Mailing List
For list archives:
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 =
