Are indexes present on both tables? Do the columns that are being joined upon both indexed?
-----Original Message----- From: James Pharaoh [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 11:28 AM To: [EMAIL PROTECTED] Subject: 1 column - 2 column join optimisation Hi, 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 The full table scan is causing my app to slow down as the history gets larger, and it's really more convenient for me to link the tables this way. I'm using myql 3.23.49. Anyone have any ideas? James [EMAIL PROTECTED] --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
