I have just learnt about temporary tables and using them has given me some really nice speed increases in some of my big queries.
The problem I have is that when I create the table, it doesn't last for the full length of the following query that uses it nor until the end of the script.
I am creating it using:
$dbh->exec("CREATE TEMPORARY TABLE _temp_unique_invoice_ref ENGINE = MEMORY AS
(SELECT jobRef, invoiceRef FROM invoices_out_reference GROUP BY invoiceRef)") ;
The query after it is a few hundred lines long and tries to make use of the temporary table many times in subqueries, but it only works for the first subquery and the rest of the query fails saying that the table does not exist.
The scenario is also further complicated by the fact that this query will be run every 10 seconds potentially by many users, so it could be executed many times before 10 seconds has elapsed.
How can I make this work without not using the TEMPORARY keyword and drop etc?
The query that follows is just one large query. Prepare is called on the same object so could that be causing a new connection? Would it work if I put the create table syntax at the start of the larger query and terminate it with ;?
You can use ::
Insert into temp_table select from table
It will automatically create a table with the description same as table, and when you are done drop it.