I have a query that involves joining 4 tables. Each of the tables has
many columns but my query involves a small subset of the columns from
each of the 4 tables.

Would it be more efficient if I created 4 temporary tables, each
containing the columns of interest and then doing the join on the
temporary tables?

My original query is fast enough, I just want to be kind to the MySQL
server.

Thank you,
Joe

On 3/21/2011 1:55 PM, Joe Hesse wrote:
Why fix something that isn't broken?

Think about it - you'll make 4 queries to create temporary tables,
selecting all rows and the columns you want from the permanent tables.

Then you'll make another query from the temporary tables, selecting
specific rows.

How can this be faster than selecting those columns from a subset of the
rows in each table?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 21-03-2011 21:05, Jerry Stuckle wrote:
in stead of being 'kind to his server', he wants to be RUDE/MEAN

--
Luuk

Joe Hesse:

I would expect not, but only someone who knows what's actually going on
inside the MySql engine can tell (and you didn't mention the engine
you're using).

But...apparently you have the database and sufficient data (so it might
matter at all). I'm curious: what was the result when you tried the
alternative method with four temporary tables, and compared it with the
obvious join?


--
Erick

On 3/21/2011 10:55 AM, Joe Hesse wrote:
The MySQL engine is reasonably smart about joins. Considerable
optimization takes place. The system tries hard to use indexes
to make the search fast.

Submit your query with the word EXPLAIN in front of the query.
This tells you how the database system will do your query. Read
the documentation for EXPLAIN to understand the output. It's
important that the EXPLAIN result not say "ALL" for some field;
that means a linear search that looks at all entries in a table,
which is usually slow.

John Nagle