Getting around MySQL "Can't reopen table" error

mysql temp-tables
Kris picture Kris · Dec 5, 2008 · Viewed 82.2k times · Source

I'm currently busy implementing a filter of sorts for which I need to generate an INNER JOIN clausse for every "tag" to filter on.

The problem is that after a whole bunch of SQL, I have a table that contains all the information I need to make my selection, but I need it again for every generated INNER JOIN

This basically looks like:

SELECT
    *
FROM search
INNER JOIN search f1 ON f1.baseID = search.baseID AND f1.condition = condition1
INNER JOIN search f2 ON f2.baseID = search.baseID AND f2.condition = condition2
...
INNER JOIN search fN ON fN.baseID = search.baseID AND fN.condition = conditionN

This works but I would much prefer the "search" table to be temporary (it can be several orders of magnitude smaller if it isn't a normal table) but that gives me a very annoying error: Can't reopen table

Some research leads me to this bug report but the folks over at MySQL don't seem to care that such a basic feature (using a table more than once) does not work with temporary tables. I'm running into a lot of scalability problems with this issue.

Is there any viable workaround that does not require me to manage potentially lots of temporary but very real tables or make me maintain a huge table with all the data in it?

Kind regards, Kris

[additional]

The GROUP_CONCAT answer does not work in my situation because my conditions are multiple columns in specific order, it would make ORs out of what I need to be ANDs. However, It did help me solve an earlier problem so now the table, temp or not, is no longer required. We were just thinking too generic for our problem. The entire application of filters has now been brought back from around a minute to well under a quarter of a second.

Answer

Pete picture Pete · Jan 28, 2013

A simple solution is to duplicate the temporary table. Works well if the table is relatively small, which is often the case with temporary tables.

Related questions

Create a temporary table in a SELECT statement without a separate CREATE TABLE

Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I …

Read More
How can I simulate an array variable in MySQL?

It appears that MySQL doesn't have array variables. What should I use instead? There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to …

Read More
Create a temporary table in MySQL with an index from a select

I have a stored function where I use temporary tables. For performance reasons, I need an index in that table. Unfortunately, I cannot use ALTER TABLE because this causes an implicit commit. Therefore I'm looking for the syntax to add …

Read More