How to handle temporary tables with mutliple connections?

Posted on

Question :

In Is there a way to hint to query optimizer to MySQL which constraints should be done first? I created a subquery.

The subquery is often not optimized.

I think a better way is to create a temporary table and then query that temporary table.

The problem is say I create a temporary table and name that “BusinessesWithinABox” table, which are businesses in the area I am interested in, say 10 by 10 km squares.

Say I have 300 users.

All of which create this temporary tables named BusinessWithinABox. Yes I know that the tables are temporary.

Then when I do query, which tables go which?

Actually some tutorial detail on how to create temporary tables and run and use that in PhP would be fine.

Answer :

A temporary table is only visible to the connection which created it. There could be 300 temporary tables, all by the same name, and non interrupting or conflicting with each other. In fact, you cannot access temporary tables created by other connections.

So when you query, you get results from the temporary table created by the same connection.

If you close a connection, the temporary table gets dropped. So, moral of the story, create a temporary table then query it, all within the same connection.

Leave a Reply

Your email address will not be published.