Re: Help optimizing dependant subqueries.

[Rick James]

KV / ER schemas are notoriously inefficient.

Consider getting all the names first. Then build the real query. Summary:
CREATE TEMPORARY TABLE ... SELECT rel, GROUP_CONCAT(...)
SELECT id, title, authors, publishers... FROM ... JOIN ...

The tmp table:

CREATE TEMPORARY TABLE tmp_names
SELECT dp.iDocID,
       dp.iDocRelationshipID as rel,
       GROUP_CONCAT( CONCAT_WS(" ", p.vcFirstName, p.vcLastName)
                          ORDER BY  p.vcLastName, p.vcFirstName
                     SEPARATOR "; ") as names
    FROM  tblDocumentPeople dp
    LEFT JOIN  tblPerson p ON p.iPersonID = dp.iPersonID
    GROUP BY  dp.iDocID, rel

Then the 'real' query uses that repeatedly as follows.

SELECT  d.iDocID, d.vcDocTitle,
        Authors.names,
        Recipients.names
    FROM  tblDocument AS d
    LEFT JOIN  tmp_names Authors ON   d.iDocID = Authors.iDocID    AND rel = 1
    LEFT JOIN  tmp_names RecipientsON d.iDocID = Recipients.iDocID AND rel = 2
    WHERE  ...

Often JOIN is more efficient than subquery.

Note: If you might get no names for Authors, consider
IFNULL(Authors.names, 'no authors')
etc.

For further analysis,please provide
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]