Does this SQL query create Temporary Tables?

Question

SELECT *
FROM (
        (SELECT table1.id1, table1.id1_type AS id1,
         FROM child AS table2 STRAIGHT_JOIN parent AS table1 ON table1.id1=table2.id1
         AND table1.id2=table2.id2
         AND table1.time=table2.time
         WHERE table1.id1=123456
           AND ((table1.time>=0
                 AND table1.time<=1361936895)))
      UNION ALL
        (SELECT table1.id1 AS id1, table1.id1_type
         FROM child AS table2 STRAIGHT_JOIN step_parent AS table1 ON table1.id1=table2.id1
         AND table1.id2=table2.id2
         AND table1.time=table2.time
         WHERE table1.id1=123456
                 AND table1.time<=1361936895)))) AS T
WHERE id1_type NOT IN (15)
ORDER BY time DESC LIMIT 2000

I'm using the following sql query (two joins, one union all) and I'm seeing heavy increased latency after adding the joins inside. I can see the storage space usage shoot up on my machines and I'm wondering if it's because I'm creating temporary tables?

As added context, when I added the joins I also added the 'table1', 'table2' aliases so that I could avoid ambiguity while choosing columns I started seeing these space usage increases.

Any suggestions on why this addition, or the query as a whole, is causing a huge storage spike on these queries would be appreciated :)

MattW MattW · Accepted Answer · 2013-03-14T04:17:17

It's up to the database engine to decide what it thinks is the best strategy to fulfill your query. Spooling to temporary tables is definitely one of the options it has.

The table aliases really shouldn't have anything to do with it, the right column is the right column whatever label you're using for it.

Out of interest, did you try it with join instead of straight_join? You're limiting the query optimizer's options by specifying straight_join.

Does this SQL query create Temporary Tables?

1 Answers