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

Question

I have stored procedures that create temp tables. I would like to then execute a query that joins with these temp tables.

The problem is that with Golang's database/sql design, the only way to ensure you get the same connection for subsequent queries is to create a transaction.

Am I asking for trouble if I wrap the majority of my SELECTs in a transaction for the purpose of accessing a temp table? I understand that I will lose some performance/scalability because I'll be holding onto connections from the pool rather than allowing them to go back between queries. But I'm wondering if I'll start seeing locking or other serious issues with this strategy.

The reason I need to do this is because the MySQL execution plan for many of my tables is very poor (I'm doing several joins across large tables). I'd like to execute some intermediate queries and store their results in temp tables to avoid this issue.

Even for temp tables? Is there no way to use temp tables in Golang then? — william, Nov 07 '15 at 02:07
The docs indicate that creating a temp table will not do an implicit commit (https://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html) however there is no way to add indexes to them as it would. — william, Nov 07 '15 at 02:08
Temporary tables in MySQL are only visible to the current connection. Once that connection closes, they are dropped. If you're using temp tables, you've got to hold on to that connection while you're doing your business. — Code4aliving, Nov 07 '15 at 04:24
Yeah, I think you're stuck with doing all of your work within a transaction. Straight from Golang documentation: "Once DB.Begin is called, the returned Tx is bound to a single connection. Once Commit or Rollback is called on the transaction, that transaction's connection is returned to DB's idle connection pool." Since temporary tables are visible only per-connection, this is all you can do. Be sure to clean up the temporary tables when you're done with them, otherwise they'll persist, and might confound the next unsuspecting inheritor of that pooled connection... — Code4aliving, Nov 07 '15 at 05:23
Just for the records: I deleted my answer suggesting to create a new sql.DB with only 1 connection because it seems that would not work (https://groups.google.com/d/msg/golang-dev/RWmOv4SYUmc/SYVuaZazQGwJ) — HectorJ, Nov 10 '15 at 02:25
@william - won't it work to create the indexes _as_ you create the tmp table? `CREATE TEMPORARY TABLE foo ( ..., INDEX(...) );` — Rick James, Dec 01 '15 at 23:03

score 7 · Accepted Answer · answered Nov 12 '15 at 02:55

You can create your own pseudo temp tables that can be accessed by multiple processes, and connections.

The idea is to simply create memory tables, run your operations, and cleanup afterwards.

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 drop it using;

DROP TABLE temp_32rfd293:

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

You'll want to clean up the occasional abandoned temp table, you can create a scheduled event in mysql to do this. If you choose to do this consider using a dedicated schema for temp tables to prevent accidental removals.

Note: You need event_scheduler=ON in your my.ini for this to work.

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 ;

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

1 Answers1