Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,992
    Thanks
    120
    Thanked 76 Times in 76 Posts

    stored proc, ENGINE MEMORY, MULTIUSER

    suppose I have stored proc:

    Code:
    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 ?

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,992
    Thanks
    120
    Thanked 76 Times in 76 Posts
    ahh
    MEMORY tables are shared among all clients (just like any other non-TEMPORARY table).
    going to look what TEMPORARY tables are ....

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,992
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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?

  • #4
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts
    http://dev.mysql.com/doc/refman/5.0/...ate-table.html

    Code:
    CREATE TEMPORARY TABLE tbl_name (...) ENGINE = MEMORY;
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."

  • Users who have thanked eak for this post:

    BubikolRamios (02-20-2009)

  • #5
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,992
    Thanks
    120
    Thanked 76 Times in 76 Posts
    even turns out that temporary keyword here gets things work faster, at least in my case.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •