Keeping in mind of course that the temporary table you create is only
accessible to the user who created it, and for the length of that
session connection.  Once your script has finished executing and the
connection to the database is released, that temporary table no longer
exists.  Net affect: every page load, the table is created when the
query is executed and destroyed when the script ends/or the connection
is closed.

What Daniel presented is an increase in code performance via query
optimization for a single connection/query execution.  The single large
result set you generate (your list of IDs) won't persist if the script
closes the connection between queries or a new page is loaded.  If you
need that "list" of IDs on the next script or page, you may need to
store the result set in a "persistent" manner such as a session cookie
or as a pseudo-persistent manner such as an auxiliary table or view in
the database (it all depends on what kind of computations are being
performed on the result set and how big it really is).

What Daniel mentioned highlights the necessity to optimize your queries
for large result sets; let the database query engine iterate over large
result sets, not PHP where possible and only output what is absolutely
necessary to the user.

I have dealt with very large output and timeouts due to the very large
result sets from our database (measured in the thousands and 100s of
thousands of rows) ultimately that is too much data for anyone to view
at once so paging, and multiple executions of queries is the outcome

Ajax patterns, using JavaScript and PHP/Perl/Python backend scripts for
example, are great ways of "chucking up" large result sets into
manageable parts then displaying them as the user needs them or as the
server can process the requests.  An inherent side effect of this is
increased traffic to the web server and the potential for many-many-many
more hits to the database.  

Max H. Thayer
Software Developer
Center for High-Throughput Structural Biology
Hauptman-Woodward Medical Research Inst.
700 Ellicott St.
Buffalo, NY 14203
Phone: 716-898-8637
Fax: 716-898-8660

-----Original Message-----
From: Daniel Carrera [] 
Sent: Wednesday, April 15, 2009 5:10 AM
Subject: Re: [PHP-DB] Is Temporary table right approach

I think a temporary table is likely to be the right approach. I have 
some times found massive seed improvements by caching a result set 
inside a temporary table.

If the data in the table is just ids (ie, just one field), you could get

an additional speed bump by rewriting your WHERE clause to use IN. For 
example, if you have:

SELECT * FROM t JOIN tempTable ON t.someId =

You can rewrite that as:

SELECT * FROM t WHERE t.someId IN (SELECT * FROM tempTable)

MySQL can apply some extra optimizations to the IN clause (e.g. sort the

data and search for a match using a binary tree search).


Manoj Singh wrote:
> Hi All,
> I have a query which returns the large number of ids which i am using
> other queries. I am doing this in PHP. Now the first query can return
> unlimited number of ids which might create problem in PHP. I want to
> this ids in MYSQL through temporary table so that i can access that
ids in
> other queries directly.
> Do you think the approach is right or there is any other good
> Please suggest.
> Regards,
> Manoj

PHP Database Mailing List (
To unsubscribe, visit:

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to