3

So I want to grab data from database, which method is faster, create several queries or one multi-query?

hakre
  • 193,403
  • 52
  • 435
  • 836
Giorgi Khmaladze
  • 113
  • 1
  • 2
  • 11
  • Related: [PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)](http://stackoverflow.com/q/6346674/367456) – hakre May 02 '15 at 12:42
  • And where was almost the same question? Please provide a link and also make clear which parts of the answers were not clear to you. Did you mean this Q&A: *"[Which is faster / more efficient - lots of little MySQL queries or one big PHP array?](http://stackoverflow.com/q/12051334/367456)"* ? – hakre May 02 '15 at 12:48
  • @hakre nope, i read [this](http://stackoverflow.com/questions/15875167/multiple-small-queries-vs-a-single-long-query-which-one-is-more-efficient). and i read even some articles about this, but it is still complicated, everybody is saying "i think", "in my opinion", "my choice is...", no one is sure about it. i want specific, true answer depend on reality. and ty for this link. – Giorgi Khmaladze May 02 '15 at 13:29
  • I can perfectly understand you want a concrete answer, however for such a concrete question is necessary, too. It's also that these subjective answers you name aren't a good fit for a Q&A site like Stackoverflow in general, the [help center](http://stackoverflow.com/help) has [more info](http://stackoverflow.com/help/dont-ask). Please take care that answers often reflect the style of the question, so by improving your question you can improve to get better answers. From what I see here, you need to improve the question if you want to prevent getting subjective answers. – hakre May 02 '15 at 13:36
  • @hakre Ok i have never used anything like stackoverflow before, i will consider your advice – Giorgi Khmaladze May 02 '15 at 14:14
  • Well, with your current question a non-subjective answer is: *"Both methods can be faster because by the fact alone to execute multiple queries or one multi-query is not the only property of speed"*. It's not subjective any longer but you don't have a sufficient answer as well. There is no generic answer to the question you ask. – hakre May 02 '15 at 14:50
  • Something that I used to help me when I meet with this dilemma is "is the number of queries going to be a function of something you can control". If it's a function of the number of related tables then it's usually preferred but if it becomes a function of the number of rows returned in an earlier query then it becomes much more difficult to predict as your data grows – Nicholas Oct 15 '15 at 06:09
  • Does this answer your question? [Which is faster / more efficient - lots of little MySQL queries or one big PHP array?](https://stackoverflow.com/questions/12051334/which-is-faster-more-efficient-lots-of-little-mysql-queries-or-one-big-php-a) – Puka Sep 09 '20 at 12:42

2 Answers2

4

Each "round trip" to the database will have some overhead. So the fewer round-trips, the less overhead. Consider also that fewer requests means fewer packets from client to server. If the result of the consolidated query gives you just what you want, then single query is the way to go. If your single query is returning extra or redundant data (perhaps because of de-normalization) then the overhead savings of a single round trip may be lost in the extra data transferred.

Another consideration is latency. If the queries have to be completed in sequence because some part of the output of one is needed in the input of the next, consolidating into one query will cut out all the network latencies in between all the individual smaller queries, so a final result can be delivered faster. However, if the smaller queries are independent of each other, launching them in parallel can get all the results delivered faster, albeit less efficiently.

Bottom line: the answer depends on the specifics of your situation. The best way to get an answer will probably be to implement both ways, test, and compare the resource usage of each implementation.

Zenilogix
  • 1,318
  • 1
  • 15
  • 31
1

In general it would be a multi query. But it depends on a lot of stuff such as the hardware, datastructure etc.

But connections do take a little time each one of them.

Gleiemeister 2000
  • 731
  • 4
  • 9
  • 23