One big query or several small ones?

Question

I'm currently saving my language files in a MySQL database.

Is it generally better (I'm thinking about performance) to fetch all page specific language strings at once (a lot fewer queries, but they are bigger and contains some unnecessary strings) or fetch at request (that gives a lot more requests, but instead, they are much smaller and won't fetch unnecessary strings).

EDIT: I'm using APC, and there's about 200-250 page specific strings, but it becomes maybe 100-150 if I fetch one request. I'm hosting MySQL on the same machine.

How many strings are we talking about? Is it just one server? Is there a cache between the database and your app like APC or memcached? — Xeoncross, Sep 06 '12 at 16:49
There isn't enough information here for anybody to answer this correctly. — Sameer, Sep 06 '12 at 16:54
Possible duplicate: http://stackoverflow.com/questions/55463/one-big-sql-select-statement-or-several-small-ones-performance?rq=1 — hometoast, Sep 06 '12 at 18:03

meustrus · Accepted Answer · 2012-09-06T17:52:29.317

It depends entirely on your situation and your available resources. Fetching everything at once will probably be better if you're making single-threaded requests to a remote server, for example, but more small requests might be faster and less memory-intensive running on a local MySQL server (Tuncay said it results in poor performance, tough). It would probably be even faster if the page were rigged up to make the requests asynchronously so that you're not waiting for the last one before making another.

However, the only way to really know is to run some benchmarks in your environment.

score 0 · Answer 2 · answered Sep 06 '12 at 16:53

My experience is that the mysql server can easily handle a big request. Several small ones instead result in very poor performance. In comparable situations I find one query is most always better in terms of performance. Get the whole data from database and let php sort out the rest.

However, just fetching the data from db in one query is even better. Are you sure you cant use an appropiate "where" clause ?

One big query or several small ones?

2 Answers2