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 INNER JOIN sites ON users.id=sites.userid WHERE sites.email='t...@test.com' ; Indexs should be on: users.username, users.id users.email sites.email, sites.userid On Thu, Aug 20, 2009 at 7:25 AM, Simon Kimber <si...@internetstuff.ltd.uk>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 <http://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 > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
