2

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.

3

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.

  • 1
    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? – NoChance 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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