Global Temporary tables uses & benefits

Help
1

Hello Guys,

Can someone help me with the Global Temporary tables concept in MemSQL. What’s the lifespan of these tables? where these are stored? It will be great if you can give me documentation.

Thanks,
Maney.

2

Hello!

The documentation for global temporary tables is here: SingleStoreDB Cloud · SingleStore Documentation

A couple of paragraphs below the “Info” note on that page.

I hope this helps. If not, let us know if you need more information.

Thanks!

3

Hello galley,

Thanks for the quick response. When i click on the provided link, it was redirecting me to the same page. Can you check it once. I would also like to know the differences between temporary and global temporary tables.

Maney.

4

I fixed the link above.

Also: The global temporary tables are similar to temporary tables except that they exist beyond the duration of a client session.

5

The main use case for global temporary tables is when you want a table that isn’t persisted in anyway (not logged to disk, not replicated to replica partitions or DR secondaries, doesn’t show up in backups), but otherwise behaves as a normal table. They are useful for storing temporary results in ETL pipelines for example were the overhead of logging is pointless overhead.

1 Like
6

Thanks Adam, what’s the life span for the global temp tables? I can see they still exist even after the session was closed. Also, when I do SHOW TABLES, the global temp tables are listed among the physical tables. Do we need to drop them manually once the work was done?

Same case with see temp tables, I can see them under SHOW TEMPORARY TABLES. Do we need to drop them manually these too? Please explain the lifespan of both the kinds.

7

Global temp tables are never automatically dropped. They behave like normal tables outside of not being persisted. You’ll need to drop them yourself when your done with them.

regular temp tables are scoped to the connection that opened them. If that connection disconnects the temp table will be automatically dropped. You can also manually drop them like a normal table if you want.