Welcome to the MacNN Forums.

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

You are here: MacNN Forums > Software - Troubleshooting and Discussion > Developer Center > MySQL/PHP question

MySQL/PHP question
Thread Tools
t_hah
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status: Offline
Reply With Quote
Feb 12, 2006, 11:38 AM
 
I have a general MySQL/PHP question.

I am looking to get the best performance out of my webpage, but also trying to keep the server CPU load low if possible.
Let assume I need to run 5 different queries in the database and then would like to format those using php.

I am using the following MySQL query structure:
$query1 = $DB->query("SELECT bla, bla...");
and diplaying it on the page like this:
foreach ($query1->result as $row) {echo $row[’bla, bla, bla’]; />}
Since I have multiple queries and I can order them a several different ways. Which one makes more sense from a MySQL/PHP point of view.

This probably uses more memory, since it stores all the db info in 5 different vairables. Also PHP probably has to wait for the queries to run, and then display them one after another.
$query1 = $DB->query("SELECT bla, bla...");
$query2 = $DB->query("SELECT bla, bla...");
$query3 = $DB->query("SELECT bla, bla...");
$query4 = $DB->query("SELECT bla, bla...");
$query5 = $DB->query("SELECT bla, bla...");

foreach ($query1->result as $row) {echo $row['bla, bla, bla']; />}
foreach ($query2->result as $row) {echo $row['bla, bla, bla']; />}
foreach ($query3->result as $row) {echo $row['bla, bla, bla']; />}
foreach ($query4->result as $row) {echo $row['bla, bla, bla']; />}
foreach ($query5->result as $row) {echo $row['bla, bla, bla']; />}
I could run the query 5 times, changing the SELECT statement of course and display them as they are pulled out of the database.
$query1 = $DB->query("SELECT bla, bla...");
foreach ($query1->result as $row) {echo $row['bla, bla, bla']; />}

$query1 = $DB->query("SELECT bla, bla...");
foreach ($query1->result as $row) {echo $row['bla, bla, bla']; />}

$query1 = $DB->query("SELECT bla, bla...");
foreach ($query1->result as $row) {echo $row['bla, bla, bla']; />}

$query1 = $DB->query("SELECT bla, bla...");
foreach ($query1->result as $row) {echo $row['bla, bla, bla']; />}

$query1 = $DB->query("SELECT bla, bla...");
foreach ($query1->result as $row) {echo $row['bla, bla, bla']; />}
I hope that my question makes sense.

So what do the experts think about this?
     
registered_user
Dedicated MacNNer
Join Date: Nov 2001
Location: Are Eye
Status: Offline
Reply With Quote
Feb 12, 2006, 12:56 PM
 
I think it's 6 of one and a half dozen of the other.

if you're curious, about performance echo the microtime at a few points along the way and measure how long your script takes to execute.
     
cla
Mac Enthusiast
Join Date: Mar 2000
Status: Offline
Reply With Quote
Feb 12, 2006, 01:20 PM
 
Easily measured, although hairsplitting if you ask me. If you really want to improve performance:

Optimize your queries, optimize your database, optimize apache............................................ .................$0.02
     
t_hah  (op)
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status: Offline
Reply With Quote
Feb 12, 2006, 01:32 PM
 
I tried registered_users' timing idea. It is really inconclusive. I created an php function, dumped all the code in there and then placed the function itself into a for cycle to run 150 times.

One run, one of the scripts is quicker, the other time the other. Of course our server can get loaded by other sites (b/c we have a shared plan)... hm...

cla: I optimize the tables on a weekly basis. I also went through our queries and optimized them as much as I could. They are not slow, but at times when we get busier, I want to make sure it is fast.

I will to keep timing the pages. Maybe I will figure something out this way.
If someone has other ideas, please don't be afraid to share.
     
cla
Mac Enthusiast
Join Date: Mar 2000
Status: Offline
Reply With Quote
Feb 12, 2006, 02:46 PM
 
I take it the database is indexed (correctly). Indexing probably constitutes the greatest performance boost to the least amount of work.

Are you confident your SQL statements are as good as they get as well? Web developers in general tend to do stuff in code which the database does better and faster.
     
registered_user
Dedicated MacNNer
Join Date: Nov 2001
Location: Are Eye
Status: Offline
Reply With Quote
Feb 12, 2006, 02:58 PM
 
The same script will rarely run in the same amount of time, you'll have to repeat the process a few times and go with averages.

also, you might be able to pick up speed with joins if you aren't already using them.
     
t_hah  (op)
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status: Offline
Reply With Quote
Feb 12, 2006, 03:01 PM
 
Yes, the database uses quite a few indexes to speed up the queries. I took extra time to make sure that the queries include as much information as possible, and then checked them with EXPLAIN.

For the question above though, I am using the same exact queries in the first and second cases, so as far as the MySQL/PHP speed goes, it should not make a difference.

I do understand what you are saying through and time to time I try to go through my scripts and see if I can optimize the better in some way.
     
t_hah  (op)
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status: Offline
Reply With Quote
Feb 12, 2006, 03:03 PM
 
Sorry, suplicated post...
     
Love Calm Quiet
Mac Elite
Join Date: Mar 2001
Location: CO
Status: Offline
Reply With Quote
Feb 12, 2006, 11:48 PM
 
My MySQL dbs are pretty small, but FWIW I notice that repeated tests of the very same query can vary incredibly. (just using PHPMyAdmin): one time it says the query took .016 sec; next: .002; next: .0005 . It plateaus there.

Must be some caching goes on? Tried same thing with query on another table. Plateaus at .0006.
TOMBSTONE: "He's trashed his last preferences"
     
madmacgames
Grizzled Veteran
Join Date: Oct 2003
Status: Offline
Reply With Quote
Feb 13, 2006, 12:26 AM
 
Originally Posted by Love Calm Quiet
Must be some caching goes on?
Yes MySQL 4+ has built in query caching that is enabled by default.
The only thing necessary for evil to flourish is for good men to do nothing
- Edmund Burke
     
t_hah  (op)
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status: Offline
Reply With Quote
Feb 13, 2006, 12:51 AM
 
Yes, the queries get cached, so you have to rerun the page a few times. But as you mentioned they kind of top out.

I will try to run these same tests, when our site is not as busy, maybe that will give me the answer I am looking for.
     
moodymonster
Mac Elite
Join Date: Sep 2003
Location: London
Status: Offline
Reply With Quote
Feb 13, 2006, 06:59 PM
 
personally I try to keep the number of MySQL queries down to a minimum. MySQL is optimized at getting information and as the amount of info increases you'll notice the difference. Also you can do some formatting etc in the MySQL query.

When you submit a query the MySQL query optimizer optimizes the query. Also it's worth testing your queries using EXPLAIN

http://dev.mysql.com/doc/refman/5.0/en/explain.html

you could also download phpBB and look at how they run queries.
( Last edited by moodymonster; Feb 13, 2006 at 07:12 PM. )
     
   
Thread Tools
 
Forum Links
Forum Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Top
Privacy Policy
All times are GMT -4. The time now is 09:26 PM.
All contents of these forums © 1995-2017 MacNN. All rights reserved.
Branding + Design: www.gesamtbild.com
vBulletin v.3.8.8 © 2000-2017, Jelsoft Enterprises Ltd.,