Simple join very slow

------_=_NextPart_001_01CA2191.7BC648F1
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi Everyone,
=20
Can anyone suggest why the following query is taking upwards of 5
seconds to run?
=20
SELECT * FROM users
JOIN sites ON users.ID =3D sites.userid
WHERE users.username =3D 'user1234'
OR users.email =3D 'test@test.com'
OR sites.email =3D 'test@test.com' =20
=20
The users table has an index on the username field and another on the
email field.
=20
The sites table has an index on the email field.
=20
Both tables contain around 200k rows.
=20
An explain of the query shows that no indexes are being used:
=20
id select_type table type possible_keys key key_len
ref rows Extra =09
1 SIMPLE users ALL PRIMARY,username,email NULL NULL
NULL 155424 =09
1 SIMPLE sites ref userid,email userid 4
dyos.users.ID 1 Using where=09
=20
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
=20
Thanks
=20
Simon

------_=_NextPart_001_01CA2191.7BC648F1--

Re: Simple join very slow

--0016e6db6c0937abdb047193c6bb
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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='test@test.com'
UNION
SELECT * FROM users
INNER JOIN sites ON users.id=sites.userid
WHERE sites.email='test@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 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 = 'test@test.com'
> OR sites.email = 'test@test.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
>



--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6db6c0937abdb047193c6bb--

Re: Simple join very slow

On Thu, Aug 20, 2009 at 10:43 AM, Johnny Withers 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-optimizat ion.html

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Simple join very slow

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 = 'test@test.com'
union
SELECT * FROM users
JOIN sites ON users.ID = sites.userid
WHERE sites.email = 'test@test.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 = 'test@test.com'
>OR sites.email = 'test@test.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=gcdmg-mysql-2@m.gmane.org