MySQL Forums
Forum List  »  Performance

Slow LEFT OUTER JOIN table on itself.
Posted by: Jared Hill
Date: June 01, 2005 09:42PM

Hi

I am joining a table on itself for the purpose of sorting and performing some distinct counts. The base query is of the OUTER JOIN is;

SELECT DISTINCT data_table1.file_id FROM attribute_data data_table1 LEFT JOIN attribute_data sort_table ON (data_table1.file_id = sort_table.file AND sort_table.attribute_id = 109888) WHERE data_table1.dataset_id = 32

There are 27100 records in the table.
The table has single column indexes on file_id, attribute_id, dataset_id, as well as some multi column indexes on relivent columns. The table has a PRIMARY KEY on an autonumber field.

The query takes over 60 seconds to execute. When I change it to an INNER JOIN, it executes in less than a second.

When using EXPLAIN, the rows being scanned is the full table for the outer join, but only 2500 for the inner join (which is the number of results of the inner join).

As a test, I imported the data into another relational database platform, and both inner and outer joins executed in 1-2 seconds, with no indexes on the table.

Any thoughts on why this might be occuring would be appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow LEFT OUTER JOIN table on itself.
3040
June 01, 2005 09:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.