[EMAIL PROTECTED] wrote: >The form "Select foo.id From foo, bar..." is giving you a cross-product. A >cross-product emits a row for every combination of rows from each of your >tables. The Explain results seem to be consistent with this. What I'd >suggest is that you try a Join on your tables. > Thanks for the info. Using a LEFT JOIN helps, although the query still takes one and a half minutes to run:
mysql> EXPLAIN SELECT bugs.bug_id FROM bugs LEFT JOIN longdescs USING (bug_id) WHERE bugs.reporter = 27300 OR longdescs.who = 27300; +-----------+------+---------------+--------+---------+-------------+--------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+------+---------------+--------+---------+-------------+--------+------------+ | bugs | ALL | reporter | NULL | NULL | NULL | 140032 | | | longdescs | ref | bug_id | bug_id | 3 | bugs.bug_id | 17 | where used | +-----------+------+---------------+--------+---------+-------------+--------+------------+ It seems like the fastest approach is a UNION between the following two queries: mysql> EXPLAIN SELECT bugs.bug_id FROM bugs LEFT JOIN longdescs USING (bug_id) WHERE bugs.reporter = 27300; +-----------+------+---------------+----------+---------+-------------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+------+---------------+----------+---------+-------------+------+-------------+ | bugs | ref | reporter | reporter | 3 | const | 127 | where used | | longdescs | ref | bug_id | bug_id | 3 | bugs.bug_id | 17 | Using index | +-----------+------+---------------+----------+---------+-------------+------+-------------+ mysql> EXPLAIN SELECT bugs.bug_id FROM longdescs LEFT JOIN bugs USING (bug_id) WHERE longdescs.who = 27300; +-----------+--------+---------------+---------+---------+------------------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+--------+---------------+---------+---------+------------------+------+-------------+ | longdescs | ref | who | who | 3 | const | 1271 | where used | | bugs | eq_ref | PRIMARY | PRIMARY | 3 | longdescs.bug_id | 1 | Using index | +-----------+--------+---------------+---------+---------+------------------+------+-------------+ Any way to do that in one query and without using UNION? -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
