Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2010-11-26
6
434 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 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 34218290
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
ID: 34218516
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
ID: 34218596
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 34218970
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
ID: 34219572
thanks
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 34219685
"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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

809 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