@NewsGrailJul 30.2008 — #Neither will affect speed of the query, only the speed of the script running and for that I would imagine they are more or less the same. If you want to improve the query efficiency I would suggest not using *, I believe writing out the fields individually is quicker even if you do want to retrieve every field. Otherwise there's not really much to be said about it.
@bokehJul 30.2008 — #writing out the fields individually[/QUOTE]Also, writing out the fields means the data will be returned ordered as per the query rather than any old way that MySQL chooses.
@ss1289Jul 30.2008 — #On the database level, those queries will perform exactly the same (Because they are exactly the same). Only the speed on the php level would be affected, but it would be so minor that it wouldn't even be noticable.
actually, the speed of the query would not be a question until you need to worry about how you retrieve the data from the SELECT. Let's say that you work on a table containing thousands and thousands of rows...
1. Don't use SELECT * ... like one of the posters mentioned above, list the fields you want to query
2. When retrieving huge data sets, try to use references instead of dumping everything into a hash (or an array - which is worse because an array = ordered hash)
3. Get some sort of paging process in place - in the SELECT query use a LIMIT to get "x" amount of records at a time -> process them -> move on to the next set (divide & conquer)
That's all I can think of at the top of my head but I'm pretty sure there are entire books written on how to get large data sets out of the db in an efficient fashion ?
@bokehJul 31.2008 — #the speed of the query would not be a question until you need to worry about how you retrieve the data from the SELECT.[/QUOTE]That's not true. There's no magic mysterious internal communication between MySQL and PHP. PHP sends the query to MySQL and MySQL returns the result set to PHP, just the same as if you were querying MySQL from the command line. Once PHP has sent the query it sits and waits until MySQL sends the result set; after all it is a single thread language. When the result set arrives PHP stores it, in its entirety, in the RAM and then continues with the script. Later when PHP uses one of its functions, for example mysql_fetch_assoc(), to access the result set it is merely accessing the RAM, not communicating with MySQL.
That's not true. There's no magic mysterious internal communication between MySQL and PHP.[/QUOTE]
Actually, that is wrong - the magic between MySQL and PHP is C. All the functions that you are calling within PHP are implemented in C.
Once PHP has sent the query it sits and waits until MySQL sends the result set; after all it is a single thread language. When the result set arrives PHP stores it, in its entirety, in the RAM and then continues with the script.[/QUOTE]
In general, yes. That is why we are having this thread ? you want to:
a) minimize the amount of data that is sitting in RAM at one time (use LIMIT in your SQL queries for example)
b) efficiently read the data from RAM (i.e. dumping into a hash instead of an array) hence using mysql_fetch_assoc (like you mention)
Later when PHP uses one of its functions, for example mysql_fetch_assoc(), to access the result set it is merely accessing the RAM, not communicating with MySQL.[/QUOTE]
Now, coming back to that "no magic between MySQL and PHP", the usual function which we use to retrieve data sets from MySQL in PHP is mysql_query(). This function is implemented by mysql_store_result() in C which buffers the whole result set into RAM (causing those time outs or slow-downs when dealing with huge tables).
When you are dealing with large data sets (potentially millions of rows) you should use mysql_unbuffered_query() instead of mysql_query() since the first one does not buffer the result into RAM as mysql_query() does.
With mysql_unbuffered_query() you also have an added bonus that you can start processing your data as soon as you receive the row information from your db. You don't need to wait until the whole query completes (that I guess would solve at least a bit the fact you mentioned PHP is not a multi-threaded language).
@bokehJul 31.2008 — #you can start processing your data as soon as you receive the row information from your db.[/QUOTE]OK, but you still need MySQL to respond before PHP continues. If the query has an ORDER BY clause MySQL has to run the query to completion just to return the first row. Also, mysql_unbuffered_query doesn't come without baggage.