Single MySQL Query one-to-many efficiency
I have seen (read) where in some instances it is more efficient to perform several queries and process the result of each in between rather than a complex SELECT query where several one-to-many or many-to-one relationships are involved.


Does that mean running a query to select data from each table and then subsequent queries to select the affiliated data? If so, repeated (redundant) queries (i.e. on one table where only a WHERE condition changes) can lead to poor performance. But, if the associated data can be shared across records (e.g. multiple accounts have the same location) then it might be better to have one query per table and then look up records in various result sets when needed.

If paging can be integrated, for example: populate the first \$n\$ results, where \$n\$ can be 20, 40, 60, etc. then the query with the joined tables might be optimal.


  Are there going to be any major issues with this single select as I have it currently constructed?


The query as it is above might likely take a while, given the high number of records in each table. But as mentioned above, paging would likely improve the situation greatly.