Nested queries performance on MySQL vs Multiple calls. (PHP)

Question

Is there any advantages to having nested queries instead of separating them?

I'm using PHP to frequently query from MySQL and would like to separate them for better organization. For example:

Is:

$query = "SELECT words.unique_attribute
          FROM words
          LEFT JOIN adjectives ON adjectives.word_id = words.id
          WHERE adjectives = 'confused'";
return $con->query($query);

Faster/Better than saying:

$query = "SELECT word_id
          FROM adjectives
          WHERE adjectives = 'confused';";
$id = getID($con->query($query));
$query = "SELECT unique_attribute
          FROM words
          WHERE id = $id;";
return $con->query($query);

The second option would give me a way to make a select function, where I wouldn't have to repeat so much query string code, but if making so many additional calls(these can get very deeply nested) will be very bad for performance, I might keep it. Or at least look out for it.

Tomas Creemers · Accepted Answer · 2013-10-25 00:37:53Z

Like most questions containing 'faster' or 'better', it's a trade-off and it depends on which part you want to speed up and what your definition of 'better' is.

Compared with the two separate queries, the combined query has the advantages of:

speed: you only need to send one query to the database system, the database only needs to parse one query string, only needs to compose one query plan, only needs to push one result back up and through the connection to PHP. The difference (when not executing these queries thousands of times) is very minimal, however.
atomicity: the query in two parts may deliver a different result from the combined query if the words table changes between the first and second query (although in this specific example this is probably not a constantly-changing table...)

At the same time the combined query also has the disadvantage of (as you already imply):

re-usability: the split queries might come in handy when you can re-use the first one and replace the second one with something that selects a different column from the words table or something from another table entirely. This disadvantage can be mitigated by using something like a query builder (not to be confused with an ORM!) to dynamically compose your queries, adding where clauses and joins as needed. For an example of a query builder, check out Zend\Db\Sql.
locking: depending on the storage engine and storage engine version you are using, tables might get locked. Most select statements do not lock tables however, and the InnoDB engine definitely doesn't. Nevertheless, if you are working with an old version of MySQL on the MyISAM storage engine and your tables are under heavy load, this may be a factor. Note that even if the combined statement locks the table, the combined query will offer faster average completion time because it is faster in total while the split queries will offer faster initial response (to the first query) while still needing a higher total time (due to the extra round trips et cetera).

Neil Mukerji · Accepted Answer · 2013-10-25 00:24:57Z

1

It would depend on the size of those tables and where you want to place the load. If those tables are large and seeing a lot of activity, then the second version with two separate queries would minimise the lock time you might see as a result of the join. However if you've got a beefy db server with fast SSD storage, you'd be best off avoiding the overhead of dipping into the database twice.

All things being equal I'd probably go with the former - it's a database problem so it should be resolved there. I imagine those tables wouldn't be written to particularly often so I'd ensure there's plenty of MySQL cache available and keep an eye on the slow query log.

answered Oct 25, 2013 at 0:24

Neil Mukerji

412 bronze badges

Collectives™ on Stack Overflow

Nested queries performance on MySQL vs Multiple calls. (PHP)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related