MySQL Forums :: Performance :: [REAL] TABLE vs TEMPORARY TABLE

Advanced Search

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)
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 2304 Shaun Martinec 12/02/2009 01:22PM
Re: [REAL] TABLE vs TEMPORARY TABLE 1191 Rick James 12/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.