Re: 1 column - 2 column join optimisation

[Brent Baisley]

Maybe I'm missing something, but those queries look the same to me.

I think it's your use of OR that is causing things to slow down. Try using a table alias and another left join instead and see if that speeds things up.

SELECT *
FROM user LEFT JOIN history ON user.userid=history.touserid
LEFT JOIN history AS hfrom ON user.userid=hfrom.touserid
WHERE user.state='ready'
GROUP BY user.userid

On Wednesday, February 5, 2003, at 12:27 PM, James Pharaoh wrote:

I'm trying to optimize the following query:

SELECT *
FROM user LEFT JOIN history ON user.userid = history.touserid OR
user.userid = history.fromuserid
WHERE user.state = 'ready'
GROUP BY user.userid

This causes a full table scan on the second table. If I do the following
however the index is used:

SELECT *
FROM user LEFT JOIN history ON user.userid = history.touserid OR
user.userid = history.fromuserid
WHERE user.state = 'ready'
GROUP BY user.userid

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php