Is there a way to use MySQL Temp Tables in Go?

Question

I have stored procedures that create temporary tables. I would then like to execute a query that connects to these temporary tables.

The problem is that when creating a Golang database / sql design, the only way to provide the same connection for subsequent queries is to create a transaction.

I'm asking about problems if I transfer most of my SELECTs to a transaction to access the temp table? I understand that I will lose some performance / scalability, because I will hold on to connections from the pool, and not let them return between requests. But I wonder if I will start looking at blocking or other serious problems with this strategy.

The reason I need to do this is because the MySQL execution plan for many of my tables is very low (I do several joins in large tables). I would like to execute some intermediate queries and store their results in temporary tables in order to avoid this problem.

Answer 1

You can create your own pseudo-temperature tables that can be accessed by several processes and connections.

The idea is to simply create memory tables, run operations, and clear later.

You can create a memory table with the following sql;

CREATE TABLE mydb.temp_32rfd293 ( id int(11) auto_increment, content varchar(50), PRIMARY KEY (`id`) ) ENGINE=MEMORY; 

Do something useful, then lower it using

 DROP TABLE temp_32rfd293: 

Scheduled event to delete tables mydb.temp_% older than 1 day

You want to clear the temporary abandoned temporary table, you can create a scheduled event in mysql for this. If you decide to do this, consider using a dedicated schema for temporary tables to prevent accidental deletion.

Note. For this you need event_scheduler=ON in my.ini.

 DELIMITER $$ CREATE EVENT `cleanup_custom_temps` ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 01:00:00' DO BEGIN --------------------------------------------------- -- Process to delete all tables with -- prefix 'temp_', and older than 1 day SET @tbls = ( SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME LIKE 'temp_%' AND CREATE_TIME < NOW() - INTERVAL 1 DAY ); SET @delStmt = CONCAT('DROP TABLE ', @tbls); PREPARE stmt FROM @delStmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; --------------------------------------------------- END */$$ DELIMITER ;