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 anyway. 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 http://www.chtsb.org http://www.hwi.buffalo.edu -----Original Message----- From: Daniel Carrera [mailto:daniel.carrera@xxxxxxxxxxxxx] Sent: Wednesday, April 15, 2009 5:10 AM To: php-db@xxxxxxxxxxxxx Subject: Re: 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 = tempTable.id 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). Daniel. Manoj Singh wrote: > Hi All, > > I have a query which returns the large number of ids which i am using in > 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 store > 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 approach? > > Please suggest. > > Regards, > Manoj > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
