Optimisation: One LARGE query with many joins, or many single queries 1

[jpo245]

Hi, I'm starting to write functions that return data from my db.

Im wondering in general if its faster to:

1. have a single query to retrieve all data once, with multiple joins to 5 tables, and then display data.

2. have multiple queries, each of which only grab data from a single table each.

I heard that joins decrease performance a lot, so which would actualyl be better?

Appreciate any help! Thanks...

 

[TonyGroves]

That really depends on the nature of the query and structure of the database. Each approach has its ups and downs. Cnsiderations like the similicity and clarity of the code, execution speed, risk of inconsistency, all come into play.

Joins can be inefficient, especially where indexes are not used to the maximum. But they can also be more efficient than multiple queries, which might involve locking tables, or creating temporary tables.

Multiple queries can sometimes be more efficient, but they have the disadvantage of being disjointed, so risking inconsistent results, unless you use table locking or temporary tables or whatever. And they can also be slower, since each query has to be separately compiled by the server.

Another approach you could consider is the use of sub-queries (available in MySQL 4.1 onwards), which can combine some of the advantages of a single query (single compilation, consistency), with some of the advantages of multiple queries (results of a previous query being fed to the next).