Solved

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

Posted on 2010-11-26
6
433 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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