Solved

Optimum number of query per page(php+mysql)?

Posted on 2010-11-26
6
432 Views
Last Modified: 2012-08-13
Hi all;

Please let me know that what is the Optimum number of query per page while working with php+mysql ?,i have heard several number but wolud like to know from an experts who has good experiences in this regard.

Thanks
0
Comment
Question by:MOSTAGHASSI
  • 2
  • 2
  • 2
6 Comments
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
Comment Utility
It depends on what you're trying to do.  A lot of web pages can be generated with one query, and when you can do that it's almost always better than using more queries.  But the number of queries might not be as important as the nature of the queries.  

When it comes time to optimize your queries, you can use EXPLAIN to get a good look at what's going on.
0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 300 total points
Comment Utility
As long as you don't get silly running hundreds or thousands of queries per page then it makes little difference. What is far more important is how your website in hosted.

If you have a dedicated server then you can run lots of queries. If you are on a SHARED server then the disk subsystem has to cope with everyone else's queries as well as yours. If you intend to run a high traffic website then it should be hosted on a dedicated server and should run as few queries as possible.

One last point. People have a tendency to write things like "SELECT * FROM myTable......" which is fine if you table only contains a few columns, but if the table has a large number of columns then this type of SELECT will impact your website as more data is being moved around and consuming memory and resources.

So, to summarise....

- If you need 100 queries per page you're doing it wrong, ideally keep 12 as a target maximum

- Exceed the query maximum if you need to

- Don't waste system resources moving data you don't want

- Consider dedicated hosting if performance is critical
0
 

Author Comment

by:MOSTAGHASSI
Comment Utility
At this time i use share server and my maximum query is around 12 but i'm sure that at future traffic will increase ,do you think that the next step will be a VSP or dedicated server?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
Comment Utility
And to amplify Brian's point about this: "SELECT * FROM myTable......" NEVER store images or the like in your data base -- you will not believe how bad performance can be until someone does SELECT * against a large table with a lot of BLOB data.

Use the LIMIT clause whenever you can.

Have indexes on every column used in WHERE, GROUP BY, ORDER BY, and JOIN.

Get familiar with how to time the execution of parts of your scripts.  You may find that a dozen queries are really very fast.

Best to all, ~Ray
<?php // RAY_script_timer.php
error_reporting(E_ALL);


// DEMONSTRATE HOW TO TIME THE EXECUTION OF A PART OF A SCRIPT PHP 5+
// MAN PAGE http://us.php.net/manual/en/function.microtime.php



// STORE THE START TIME
$alpha_time = microtime(TRUE);



// PERFORM SOME ACTIVITY THAT YOU WANT TO TIME
$page = 'http://google.com';
$html = file_get_contents($page);



// STORE THE COMPLETION TIME
$omega_time = microtime(TRUE);




// COMPUTE THE ELAPSED TIME AT A MEANINGFUL LEVEL
$lapse_time = $omega_time - $alpha_time;
$lapse_msec = $lapse_time * 1000.0;
$lapse_echo = number_format($lapse_msec, 1);

// SHOW A DESCRIPTION OF THE WORK AND THE TIME REQUIRED
echo "SCRIPT READ $page IN $lapse_echo MILLISECONDS";

Open in new window

0
 

Author Closing Comment

by:MOSTAGHASSI
Comment Utility
thanks
0
 
LVL 34

Expert Comment

by:Beverley Portlock
Comment Utility
"At this time i use share server and my maximum query is around 12...."

Nothing to worry about at this stage


"...but i'm sure that at future traffic will increase ,do you think that the next step will be a VSP or dedicated server?"

If the traffic increases significantly then the shared server's performance will depend on both your programming and the performance of other websites on that host. I have seen some shared hosts where the machine's load average gets up into the hundreds which makes the websites hosted on it very, very sluggish.

If the overall website response begins to degrade then start looking at a dedicated host. If performance is acceptable and remains so then leave it on the shared host. Also remember that if a website is hacked on a shared host then the host server may go down or become very unresponsive in which case your website will suffer.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now