Menu
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.
690
In MySQL, a temporary table is a special type of table that allows you to store a temporary result set, which you can reuse several times in a single session. A temporary table is very handy when it is impossible or expensive to query data that requires a single SELECT statement with the JOIN clauses.
I have often found that temp tables speed things up, sometimes dramatically. The simple explanation is that it makes it easier for the optimiser to avoid repeating work.
You can work around this issue if your query permits use of a common table expression (CTE) rather than a TEMPORARY table. For example, this fails with the Can't reopen table error: To avoid the error, use a WITH clause that defines a CTE, rather than the TEMPORARY table:
* You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work: mysql> SELECT * FROM temp_table, temp_table AS t2; ERROR 1137: Can't reopen table: 'temp_table' * The SHOW TABLES statement does not list TEMPORARY tables. * You cannot use RENAME to rename a TEMPORARY table.
You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work: The statement produces this error: You can work around this issue if your query permits use of a common table expression (CTE) rather than a TEMPORARY table. For example, this fails with the Can't reopen table error:
Then you'd also need a cleanup job to remove old ones occasionally (MySQL conveniently remembers the time a table was created, so you could just use that to work out when a clean up was required) Temporary tables has the extreme advantage that you can have multiple queries running simultaneously. This is not possible with permanent tables.
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.
172
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
