MySQL Forums
Forum List  »  Performance

[REAL] TABLE vs TEMPORARY TABLE
Posted by: Shaun Martinec
Date: December 02, 2009 01:22PM

I am running the query below to simulate a FULL OUTER JOIN of 2 tables:

(SELECT * FROM Ttable1
LEFT JOIN Ttable2 ON Ttable2.joinKey = Ttable1.joinKey)
UNION ALL
(SELECT * FROM TTtable2
LEFT JOIN TTtable1 ON TTtable1.joinKey = TTtable2.joinKey
WHERE TTtable1.joinKey IS NULL)

I should note that Ttable1 = TTtable1 and Ttable2 = TTtable2. I had to create a copy of each because a temporary table can only be referenced once in a single query. There is an index on joinKey in all tables. The tables contain around 50,000 records each and are not very wide.

Here is my problem:

When using TEMPORARY TABLES the result is very slow, but if I convert the TEMPORARY TABLES into real tables, it completes rather quickly. For some reason the sort is extremely slow when using TEMPORARY TABLES. I was wondering if there was a way to speed up the query when using TEMPORARY TABLES. Would the query complete faster if I removed the index altogether?

Options: ReplyQuote


Subject
Views
Written By
Posted
[REAL] TABLE vs TEMPORARY TABLE
2605
December 02, 2009 01:22PM
1295
December 03, 2009 11:26PM


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.