Solved

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

Posted on 2010-11-26
6
439 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 110

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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
LVL 110

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

Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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…
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.

617 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