CODEWITHSUNDEEP
mysql
ZorroDeLaArena
ZorroDeLaArena

Reputation: 547

Can MySQL fall back to another table type if a temp memory table fills up?

When creating a temp table, I don't have a good way to estimate how much space it'll take up so sometimes running a query like

CREATE TEMPORARY TABLE t_temp ENGINE=MEMORY
SELECT t.*
FROM `table_name` t
WHERE t.`column` = 'a';

Results in the error "The table 't_temp' is full". I realize you can adjust your max_heap_table_size and tmp_table_size to allow for bigger tables but that's not a great option because these tables can get quite large.

Ideally, I'd like it to fall back to a MyISAM table instead of just erroring out. Is there some way to specify that in the query or in the server settings? Or is the best solution really just to watch for errors and then try running the query again with a different table type? That's the only solution I can think of, besides just never using MEMORY tables if there's any doubt, but it seems wasteful of database resources and is going to create more code complexity.

I'm running MySQL v5.5.27, if that affects the answer.

Upvotes: 0

Views: 131

Answers (2)

Ondra Žižka
Ondra Žižka

Reputation: 46874

MySQL Cluster offers the same features as the MEMORY engine with higher performance levels, and provides additional features not available with MEMORY:

...Optional disk-backed operation for data durability.

Source: MySQL 5.5 manual. http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html

Not sure if Cluster can be combined a temp table.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108796

The memory engine is just that: if you run out of RAM, you're done unless you want to develop your own storage engine as @eggyal proposed.

With respect, there are probably better ways to optimize your system than mucking about with conditional memory tables. If I were you I'd just take ENGINE=MEMORY out of your code and move on to the next problem. MySQL is pretty good about caching tables and using the RAM it has effectively with the other storage engines.

Upvotes: 2

Related Questions