What is the optimal MYSQL query number in php sript?

Question

I am not professional programmer so i can not be sure about this.How many mysql queries your scripts send at one page and what is your optimal query number .For example at stackoverflow's homepage it lists questions shows authors of these questions . is stackoverflow sends mysql query foreach question to get information of author. or it sends 1 query and gets all user data and match it with questions ?

Answer 1

There really is no optimal number of queries. Obviously the less queries you make the better.

If you are using some kind of ORM like Hibernate, Propel, Doctrine, etc they will generate queries differently than if you were to write the SQL by hand. So if StackOverflow uses an ORM they might have more than one query accessing the questions and the users that created the questions. Or they might just use a join with straight SQL.

It really depends on the technology you are using and what it actually does behind the scenes to generate the SQL.

Things you should be researching to understand this better:

• Lazy loading
• Object Relational Mapping

Answer 2

I like to keep mine under 8.

Seriously though, that's pretty meaningless. If hypothetically there was a reason for you to have 800 queries in a page, then you could go ahead and do it. You'll probably find that the number of queries per page will simply be dependant on what you're doing, though in normal circumstances I'd be surprised to see over 50 (though these days, it can be hard to realise just how many you're doing if you are abstracting your DB calls away).

Slow queries matter more

I used to be frustrated at a certain PHP based forum software which had 35 queries in a page and ran really slow, but that was a long time ago and I know now that the reason that particular installation ran slow had nothing to do with having 35 queries in a page. For example, only one or two of those queries took most of the time. It just had a couple of really slow queries, that were fixed by well-placed indexes.

I think that identifying and fixing slow queries should come before identifying and eliminating unnecessary queries, as it can potentially make a lot more difference.
Consider even that three fast queries might be significantly quicker than one slow query - number of queries does not necessarily relate to speed.

I have one page (which is actually kind of a test case/diagnostic tool designed to be run only by an admin) which has over 800 queries but it runs in a matter of seconds. I guess they are all really simple queries.

Try caching

There are various ways to cache parts of your application which can really cut down on the number of queries you do, without reducing functionality. Libraries like memcached make this trivially easy these days and yet run really fast. This can also help improve performance a lot more than reducing the number of queries.

If queries are really unnecessary, and the performance really is making a difference, then remove/combine them

Just consider looking for slow queries and optimizing them, or caching their results, first.

Answer 3

0 would be optimal if you are prioritizing speed.

Answer 4

I recently started refactoring some older code of mine and I realised that I had used a lot of queries inside loops because back then I didn't know how to write SQL queries with subqueries and joins, etc. So I went and integrated these nested queries into one query so I could retrieve all the data at once and then loop over it in a nested way.

In some cases this made the page load significantly faster.

Ergo: It's definitely worth learning about the possibilities of SQL so you can start doing more with SQL and less with PHP.

Answer 5

I would not say that there is an optimal number of queries to be on any given script, but rather you have a goal when optimising; ordinarily time is the main concern, among other things. If time is the only concern, you could optimise you queries such that you could have queries that are executed in less time than one other queries. This is how I view optimosation, I have an objective, how best do I achieve it. Is there any information that you can cache? Based on you indexes, would a particular order of filters in your query perform better.....

My point, optimisation is best done on both the Db end and the application end. You may want to read more on database optimisation.

Answer 6

As few as you need and no more. There is no rule of thumb here. Some websites require a lot of db access and others don't.

SO actually has only a few db calls if its written as I think. On a page like this one, an answer to a question, there would:

1) session verification, if you are logged in. 2) current user info, to get the user bar at the top of the screen and you medal count. 3) get the question info as well as the questioner's/last editor's info. 4) retrieve a count of tags used in this question. 5) select all responses and responder data in one shot.

And that's about it. The fun part is how much is keyed off the question:

// this returns one row per revision
select q.*, u.name, u.u_id, u.points, u.gmedal, u.smedals, u.bmedals
from questions q left outer join users on q.u_id = u.u_id
where q_id = :q_id;

// this used to display the tags below the question and the tag counts on the right
select t.name, count(*)
from tags t left join tags q on q.tagid = t.tagid
where t.q_id = :q_id

// this can also get multiple revisions
select a.*, u.name, u.u_id, u.points, u.gmedal, u.smedals, u.bmedals
from answers a left outer join users on a.u_id = u.u_id
where a.q_id = :q_id

This assumes that the various counts (vote-ups, favored question) are cached on the table as well as stored separately.

Answer 7

Don't focus on the number of queries. This is not a useful metric. Instead, you need to look at a few other things:

• how many queries are duplicated?
• how many queries have intersecting datasets? or are a subset of another?
• how long do they take to run? have you profiled the common ones to check indices?

• how many are unnecessarily complex?

  Numerous times I've seen three simpler queries together execute in a tenth of the time of one complex one that returned the same information. By the same token, SQL is powerful, but don't go mad trying to do something in SQL that would be easier and simpler in a loop in PHP.

• how much progressive processing are you doing?

  If you can't avoid longer queries with large datasets, try to re-arrange the algorithm so that you can process the dataset as it comes from the database. This lets you use an unbuffered query in MySQL and that improves your memory usage. And if you can provide output whilst you're doing this, you can improve your page's perceived speed by provinding first output sooner.

• how much can you cache some of this data? Even caching it for a few seconds can help immensely.

Answer 8

The optimal number is as many as you need to display the information the user expects. I always try to keep it in the single digits. For information that takes a few queries, but rarely changes, I cache the results in a generic cache table so it only takes one query. Store it as a serialized array to retain an easy to access structure.

When I first installed WordPress, I was appalled that the base install did over 20 queries! Plugins would increase that number (some by quite a bit). But with caching, that could be reduced to zero (SuperCache). If your content changes every 10 minutes, why generate it dynamically every hit?

At the very extreme is a platform like Facebook, where every page is unique content, customized to the user viewing it. You have to query every time.

But regardless, I rarely see the need to hit double digits query counts.