SELECT count(application.*), application.media_ID, mailbox.company_ID FROM application, mailbox WHERE mailbox.app_ID = application.ID GROUPBY mailbox.company_ID, application.media_ID
SQL is unchecked;
try to look at the problem from the other direction determine what information you want to extract the dive into the mySQL manual to determine if there is any SQL syntax that provides the data inherent in the database in the form you want it.
with regard to speed - oh boy it will!
they way I see it, in an 'information age' data is king - which means the database, being the closest to the king, should pre-process as much as possible - only do it is as little queries as poss. because each trip to the data base is overhead:
n - num of rows T - time for 1 good query t - time for 1 bad query O - connection overhead
single query | many queries O+T | n*t + n*O
seeing as databases are optimized to perform the kind of action you require you can expect the saving to be considerable in technical terms - in real life you may see little change, none the less you know that you script is more effecient and can therefore handle a higher load.
notice the layout of the SQL;
$sql = ' SELECT count(application.*), application.media_ID, mailbox.company_ID FROM application, mailbox WHERE mailbox.app_ID = application.ID GROUPBY mailbox.company_ID, application.media_ID';
little things like this help when you _have_ to go in and optimize the code a year after you last touched it.
if its your first app, you probably see no end to, after a few you begin to realise that they're all just one every evolving/mutating collection of code to which there is no end. the moral of the story trust your instincts. :-)
Ryan Marks wrote:
Hello all,
My script works fine as it is, but I am trying to reduce the number of queries to the database. I am running PHP 4.3.4 and MySQL 4.0.3.
Here is my table structure:
...
TIA, Ryan Marks
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
