How do I optimize the following query? SELECT foo.id FROM foo, bar WHERE foo.id = bar.foo_id AND foo.field = 'baz' OR bar.field = 'baz';
Only a small number of records from each table match the query conditions, but MySQL takes forever to execute it. No wonder, when EXPLAIN says it needs to look at 1,305,144 x 139,929 rows: mysql> EXPLAIN SELECT bugs.bug_id FROM bugs, longdescs WHERE bugs.bug_id = longdescs.bug_id AND bugs.reporter = 27300 OR longdescs.who = 27300; +-----------+------+------------------+------+---------+------+---------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+------+------------------+------+---------+------+---------+------------+ | longdescs | ALL | bug_id,who | NULL | NULL | NULL | 1305144 | | | bugs | ALL | PRIMARY,reporter | NULL | NULL | NULL | 139929 | where used | +-----------+------+------------------+------+---------+------+---------+------------+ It seems like MySQL could treat this query as the UNION of two separate queries, each of which matches one of the OR conditions, and execute it exceedingly rapidly. Is there any way to get it to do that besides explicitly using UNION, which is not supported on my version of MySQL (3.23.44)? -myk --------------------------------------------------------------------- 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
