stored proc, ENGINE MEMORY, MULTIUSER

[BubikolRamios]

suppose I have stored proc:


drop table if exists tbl;
create table tbl ... engine memory

do
/*fill data, manipulate data in tbl, takes a while*/
loop



1.I presume that there should be no prob if multiple users would do this at once, as each time for each procedure call , new separate copy of tbl would be created in mem ?
2. if this would be done without engine memory then this would be realy catastrophic ?

[BubikolRamios]

ahh

MEMORY tables are shared among all clients (just like any other non-TEMPORARY table).


going to look what TEMPORARY tables are ....

[BubikolRamios]

so it turns out memory tables are global across all connections
temp tables are only per 1 connection, but tested it, much slower, surrprised coz I read somewhere they are allso in memory unless "big_tables= 1", which means they are written to disk.

So the question here is how to make memory table per one connection only.
or temporary table speedy like memory table, or lock memory table while using it?

[eak]

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

CREATE TEMPORARY TABLE tbl_name (...) ENGINE = MEMORY;

[BubikolRamios]

even turns out that temporary keyword here gets things work faster, at least in my case.