|
|
MySQL/PHP question
|
|
|
|
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status:
Offline
|
|
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?
|
|
|
|
|
|
|
|
|
Dedicated MacNNer
Join Date: Nov 2001
Location: Are Eye
Status:
Offline
|
|
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.
|
|
|
|
|
|
|
|
|
Mac Enthusiast
Join Date: Mar 2000
Status:
Offline
|
|
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
|
|
|
|
|
|
|
|
|
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status:
Offline
|
|
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.
|
|
|
|
|
|
|
|
|
Mac Enthusiast
Join Date: Mar 2000
Status:
Offline
|
|
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.
|
|
|
|
|
|
|
|
|
Dedicated MacNNer
Join Date: Nov 2001
Location: Are Eye
Status:
Offline
|
|
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.
|
|
|
|
|
|
|
|
|
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status:
Offline
|
|
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.
|
|
|
|
|
|
|
|
|
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status:
Offline
|
|
Sorry, suplicated post...
|
|
|
|
|
|
|
|
|
Mac Elite
Join Date: Mar 2001
Location: CO
Status:
Offline
|
|
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"
|
|
|
|
|
|
|
|
Grizzled Veteran
Join Date: Oct 2003
Status:
Offline
|
|
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
|
|
|
|
|
|
|
|
Mac Elite
Join Date: Dec 2000
Location: Tempe, AZ
Status:
Offline
|
|
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.
|
|
|
|
|
|
|
|
|
Mac Elite
Join Date: Sep 2003
Location: London
Status:
Offline
|
|
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.
)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Forum Rules
|
|
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
|
HTML code is Off
|
|
|
|
|
|
|
|
|
|
|
|