CODEWITHSUNDEEP
mysqltemp-tables
kazinix
kazinix

Reputation: 30093

Is temporary table reusable in MySQL?

So my boss gave me a stored procedure that needs to be optimized since it is slow. The procedure is creating a temporary table in the process and it looks weird:

/* pseudocode for brevity */
create temp_table;

insert into temp_table (...) 
    select ... 
    from other_table 
    inner join ...
    inner join ...
    where condition

select * from temp_table

So that is it. The code inserts data into the temporary table only to be retrieved right after. Here's what I did, I removed the temporary table and return directly the selected records:

/* pseudocode for brevity */
select ... 
from other_table 
inner join ...
inner join ...
where condition

The code before looks wrong, but I want to be sure what a temporary table does. So I read about temporary tables and figured that temporary tables lasts and can only be accessed in a single session. My question:

  1. What does session mean? When I'm in the application side and open a connection, can I reuse the temporary table? I wonder if that's why there is a temporary table here.

Upvotes: 0

Views: 501

Answers (2)

Chopping
Chopping

Reputation: 351

As for SQL connection, Session means one connection u create. Once you make a connection to your database,the specified session will be created by database server.

And as an admitted newbie dev on SQL, I always choose temporary table when I need to do a branch of queries through many tables.(PS: my experience shows me that those rows stored in temporary table will no be updated though their value have been changed)

Similar question here.

Upvotes: 0

Jaffar sadiq
Jaffar sadiq

Reputation: 11

A session in Database mean a operation between opening an closing of a connection.

Eg. In a application side if i try to call a stored procedure and before that i open a connection to DB.The scope of a temporary table available until i say connection closed

Upvotes: 1

Related Questions