mysql in memory

[Off]

How do I make MySQL run in memory rather than store all the data in files? I realize this can be a risk (if the server crashes I loose all data) but I understand it is much faster. Does anyone have any experience with this and how this is done? Does it really run faster?

[WingedPanther]

You could create a virtual drive in RAM. I would never recommend doing this, however.

[Off]

Because you will loose all of your data/information if the computer is shut-down?

[WingedPanther]

Exactly. Power outages won't give you a chance to let you save the file.

[Jordan]

You can create a table in memory:

CREATE TABLE t (i INT) ENGINE = MEMORY;

This will cause all data in that table to be in memory. Take heed to WPs warning though.

More info: MySQL :: MySQL 5.0 Reference Manual :: 13.4 The MEMORY (HEAP) Storage Engine

[amrosama]

cool, can be useful when dealing with temporary tables

[TcM]

So servers use this? Because when it crashes usually the data is lost and they just restore a backup.. while if it was stored on the HDD the data would be recovered...

[Jordan]

Probably for temporary tables only. I can't think of reason you would store permanent data in memory.