How many MySQL queries should I limit myself to on a page? PHP / MySQL

Question

Okay, so im sure plenty of you have built crazy database intensive pages...

I am building a page that I'd like to pull all sorts of different / unrelated databse information from....here are some sample different queries for this one page:

-article content and info
-IF the author is a registered user, their info -UPDATE the article's view counter -retrieve comments on the article -retrieve information for the authors of the comments -if the reader of the article is signed in, query for info on them

etc etc...

i know these are basically gonna be pretty lightning quick...and i can combine some...but i wanted to make sure that this isn't abnormal?

how many fairly normal and unheavy queries would you limit yourself to on a page?

Answer 1

I don't think there is any one correct answer to this. I'd say as long as the queries are fast, and the page follows a logical flow, there shouldn't be any arbitrary cap imposed on them. I've seen pages fly with a dozen queries, and I've seen them crawl with one.

Answer 2

As many as needed, but not more.

Really: don't worry about optimization (right now). Build it first, measure performance second, and IFF there is a performance problem somewhere, then start with optimization.

Otherwise, you risk spending a lot of time on optimizing something that doesn't need optimization.

Answer 3

If you need the queries, you should just use them.

What I always try to do, is to have them executed all at once at the same place, so that there is no need for different parts (if they're separated...) of the page to make database connections. I figure it´s more efficient to store everything in variables than have every part of a page connect to the database.

Answer 4

Every query requires a round-trip to your database server, so the cost of many queries grows larger with the latency to it.

If it runs on the same host there will still be a slight speed penalty, not only because a socket is between your application but also because the server has to parse your query, build the response, check access and whatever else overhead you got with SQL servers.

So in general it's better to have less queries.

You should try to do as much as possible in SQL, though: don't get stuff as input for some algorithm in your client language when the same algorithm could be implemented without hassle in SQL itself. This will not only reduce the number of your queries but also help a great deal in selecting only the rows you need.

Piskvor's answer still applies in any case.

Answer 5

I've had pages with 50 queries on them without a problem. A fast query to a non-large (ie, fits in main memory) table can happen in 1 millisecond or less, so you can do quite a few of those.

If a page loads in less than 200 ms, you will have a snappy site. A big chunk of that is being used by latency between your server and the browser, so I like to aim for < 100ms of time spent on the server. Do as many queries as you want in that time period.

The big bottleneck is probably going to be the amount of time you have to spend on the project, so optimize for that first :) Optimize the code later, if you have to. That being said, if you are going to write any code related to this problem, write something that makes it obvious how long your queries are taking. That way you can at least find out you have a problem.

Answer 6

Wordpress, for instance, can pull up to 30 queries a page. There are several things you can use to stop MySQL pull down - one of them being memchache - but right now and, as you say, if it will be straightforward just make sure all data you pull is properly indexed in MySQL and don't worry much about the number of queries.

If you're using a Framework (CodeIgniter for example) you can generally pull data for the page creation times and check whats pulling your site down.

Answer 7

As other have said, there is no single number. Whenever possible please use SQL for what it was built for and retrieve sets of data together.

Generally an indication that you may be doing something wrong is when you have a SQL inside a loop.

When possible Use joins to retrieve data that belongs together versus sending several statements.

Always try to make sure your statements retrieve exactly what you need with no extra fields/rows.

Answer 8

Premature optimisation is a problem like people have mentioned before, but that's where you're crapping up your code to make it run 'fast'. But people take this 'maxim' too far.

If you want to design with scalability in mind, just make sure whatever you do to load data is sufficiently abstracted and calls are centralized, this will make it easier when you need to implement a shared memory cache, as you'll only have to change a few things in a few places.

Answer 9

In my experience, it is better to make two queries and post-process the results than to make one that takes ten times longer to run that you don't have to post-process. That said, it is also better to not repeat queries if you already have the result, and there are many different ways this can be achieved.

But all of that is oriented around performance optimization. So unless you really know what you're doing (hint: most people in this situation don't), just make the queries you need for the data you need and refactor it later.

Answer 10

I think that you should be limiting yourself to as few queries as possible. Try and combine queries to mutlitask and save time.