Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In Is there a way to hint to query optimizer to mysql which contraints 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.

share|improve this question

1 Answer 1

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.

share|improve this answer
    
At least in .NET, "connection pooling" is used. So client A may finish using a connection and client B picks that same connection, do you see this as an issue, since B may find that temp objects created by A are still there? –  Emmad Kareem Aug 7 '12 at 11:50
    
Yes, it is an issue, actually. To be on the safe side, start with DROP TEMPORARY TABLE IF EXISTS table_name before table creation. –  Shlomi Noach Aug 7 '12 at 12:09
    
Thanks for the clarification. –  Emmad Kareem Aug 7 '12 at 14:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.