Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-11-26
6
Medium Priority
?
440 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
[X]
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
  • 2
  • 2
  • 2
6 Comments
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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 1200 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

722 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