Efficiency of 3 mysql selects vs 1 in php

Question

What's the difference between these time-complexity wise?

mysql_query("SELECT * FROM table WHERE id = 0 OR id = 1 OR id = 2");

and

mysql_query("SELECT * FROM table WHERE id = 0");
mysql_query("SELECT * FROM table WHERE id = 1");
mysql_query("SELECT * FROM table WHERE id = 2");

How does it scale with a lot of rows in the table?

How does it scale with a lot of mysql_query calls instead of three?

I'm mostly just wondering if it's much more inefficient to do multiple calls instead of one and how that scales.

I don't want to know the exact time I just want to know the ballpark complexity which must be somewhat common knowledge that isn't easy to find. Is it exponential? Are they equivalent? — Max Hudson, Apr 24 '15 at 21:31
Ballpark is that three separate statements are three times slower than one statement — Mark Baker, Apr 24 '15 at 21:34
just for 3 ids it is faster to do `WHERE id = 0 OR id = 1 OR id = 2`. Because of less travel from your webserver to mysql. If it's significantly more - it's less efficient plus there may be a limit for max SQL length. Write a test to see as @u_mulder suggests. It so depends - how big table is, indexes ... no single answer for this. — TarasB, Apr 24 '15 at 21:34
You can also simplify the single statement as `SELECT * FROM table WHERE id IN(0, 1, 2)` — Mark Baker, Apr 24 '15 at 21:35
the simpliest case is 1 query better than 3 but as @u-mulder pointed it's your responsibility to check — cske, Apr 24 '15 at 21:35
Depending on your database engine (InnoDB vs MyISAM), the one liner may be quicker in some instance due to row vs table level locking. — Ye., Apr 24 '15 at 21:36
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 24 '15 at 21:37
Answers are usually hard to come by because they are generally 'it depends'. Your query is too generic and while for this one it is rather easy to guess, in real world it may depend on the database engine, usage of keys, size of row, locking, database/table usage, rtt (unix socket vs. tcp), etc. — Fox, Apr 24 '15 at 21:38
Thanks @TarasB. I was planning on testing anyway I just thought it might be good to ask first in case it was a really obvious answer. — Max Hudson, Apr 24 '15 at 21:38
@JayBlanchard I'm not even using mysql, I'm using postgres, but I figured the answer would be the same and would help more users reading the post. I will change. — Max Hudson, Apr 24 '15 at 21:39
I retagged as PostgreSQL as per your comment, then realised you've used MySQL-specific functions in your question. Please update these to the functions you're actually using (or plan to use). — halfer, Apr 24 '15 at 23:06

score 4 · Accepted Answer · edited May 23 '17 at 12:28

4

I had a similar question a while back, but about inserts. Here's the answer that was provided, I just looked it up and this applies to mysql equally as well as mssql.

Much of the overhead of any mysql query is development of an execution plan. If you do this as a single select, it will have to develop the execution plan one (1) time; if you do 512 seperate selects, it will have to develop the execution plan 512 times. So it is considerably less overhead to do a single select.

I think this is the general knowledge broad answer you were looking for. In general, you want to make as few connections to the database (because this causes overhead), and as few queries as possible (because many queries = calculating many execution plans, more overhead)

edited May 23 '17 at 12:28

Community

1
1

answered Apr 24 '15 at 21:35

J-Dizzle

3,176
6
31
49

2

There's also the overhead of general communication between the PHP code and the database server x 3 for three queries – Mark Baker Apr 24 '15 at 21:36
@J-Dizzle Had to wait for 8 mins – Max Hudson Apr 24 '15 at 21:58

score 0 · Answer 2 · answered Apr 24 '15 at 21:38

I think there is a little known (wzs to me, anyway) feature in MySQL and PHP which are the existence of asynchronous queries. You can throw out multiple queries at once, then poll and finally reap them as they become available.

http://www.percona.com/blog/2013/03/06/accessing-xtradb-cluster-nodes-in-parallel-from-php-using-mysql-asynchronous-calls/

Another kink for your benchmarking search.

Efficiency of 3 mysql selects vs 1 in php

2 Answers2