2

Is it better to have like twenty separate queries like SELECT id FROM table WHERE value = 'hello' and then combine everything in a single 1 dimensional array in php or one huge query like

SELECT `t1`.`id` FROM (SELECT `id` FROM `table` WHERE `value` = 'hello') AS `t1`
LEFT JOIN (SELECT `id` FROM `table` WHERE `value` = 'world') AS `t2`
ON `t1`.`id` = `t2`.`id`
...
UNION ALL
SELECT `t1`.`id` FROM (SELECT `id` FROM `table` WHERE `value` = 'hello') AS `t1`
RIGHT JOIN (SELECT `id` FROM `table` WHERE `value` = 'world') AS `t2`
ON `t1`.`id` = `t2`.`id`
...

and so on until I've managed to join the results from all twenty sources?

Nadroev
  • 363
  • 1
  • 11
  • 5
    Since this will depend on a lot of things, including table size, network round trip time, etc. the best thing you can do is benchmark this yourself. – Jory Geerts May 26 '15 at 10:20
  • 2
    Try both ways, measure the time, draw your own conclusion. It depends of your queries and your data. – axiac May 26 '15 at 10:20
  • 2
    Usually anything that reduces the number of queries you make to the DB is better. But for something like this, I suspect the difference will be minor. The bottleneck is in transfering the data between the server and client, and the same amount of data is transferred both ways. – Barmar May 26 '15 at 10:23
  • [Premature optimization is the root of all evil](http://c2.com/cgi/wiki?PrematureOptimization) – Barmar May 26 '15 at 10:24
  • @JoryGeerts I am asking because the latter option seems too complex and I don't know if it's worth spending several hours figuring out how to do it. Table size is really big btw, currently around 2000 rows and increasing. – Nadroev May 26 '15 at 10:26
  • Are these the real queries? The outer `SELECT` is a complicated way to write `SELECT id FROM table WHERE value = 'hello'`. The two sub-queries produce two sets of `id` values that have nothing in common. Joining them with `INNER JOIN` produces an empty set, `LEFT JOIN` generates one row for each entry from the left set (`SELECT ... WHERE value='hello'`). – axiac May 26 '15 at 10:28
  • 1
    If the number of queries is bound to the number of records and the number of records is growing, you'll find at some point that the multiple queries option will get progressively slower and slower. The big query option my be more complex, but it's more likely future-proof. Of course all this is extremely dependant on you structure and usecase. – Kevin Nagurski May 26 '15 at 10:30
  • @Barmar *Love of* premature optimization is the root of all evil ;-) – Strawberry May 26 '15 at 10:32
  • @axiac the code is supposed to emulate a full outer join like shown here: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – Nadroev May 26 '15 at 10:36

0 Answers0