...

View Full Version : stored proc, ENGINE MEMORY, MULTIUSER



BubikolRamios
02-17-2009, 11:16 AM
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
02-19-2009, 03:05 AM
ahh


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


going to look what TEMPORARY tables are ....

BubikolRamios
02-20-2009, 08:33 AM
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
02-20-2009, 03:20 PM
http://dev.mysql.com/doc/refman/5.0/en/create-table.html


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

BubikolRamios
02-20-2009, 07:53 PM
even turns out that temporary keyword here gets things work faster, at least in my case.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum