Help optimizing dependant subqueries.

[Rob Hofmeyr]

Hi all,

I'm running a query that uses dependant subqueries - and as a result - I'm worried about performance as the database grows.

I've uploaded a (very) simplified database schema here:
http://dl.getdropbox.com/u/1732054/schema.gif

A document can have multiple authors and recipients. The person relationship is represented by iDocRelationshipID (1 is author, 2 is recipient etc).

Below is the SQL I'm using to view all documents, their authors and recipients. (tried <pre> and <code> tags, but to no avail?)

SELECT
tblDocument.iDocID,
tblDocument.vcDocTitle,
(
SELECT GROUP_CONCAT(
CONCAT_WS(" ", tblPerson.vcFirstName, tblPerson.vcLastName) ORDER BY tblPerson.vcLastName, tblPerson.vcFirstName
SEPARATOR "; ")
FROM tblDocumentPeople
LEFT JOIN tblPerson ON tblPerson.iPersonID = tblDocumentPeople.iPersonID
WHERE tblDocumentPeople.iDocRelationshipID = 1
AND tblDocument.iDocID = tblDocumentPeople.iDocID
GROUP BY tblDocumentPeople.iDocID
) AS Authors,
(
SELECT GROUP_CONCAT(
CONCAT_WS(" ", tblPerson.vcFirstName, tblPerson.vcLastName) ORDER BY tblPerson.vcLastName, tblPerson.vcFirstName
SEPARATOR "; ")
FROM tblDocumentPeople
LEFT JOIN tblPerson ON tblPerson.iPersonID = tblDocumentPeople.iPersonID
WHERE tblDocumentPeople.iDocRelationshipID = 2
AND tblDocument.iDocID = tblDocumentPeople.iDocID
GROUP BY tblDocumentPeople.iDocID
) AS Recipients
FROM tblDocument
WHERE ...

Can anyone suggest a better way to handle this query instead of relying on dependant subqueries? I was thinking of creating tblDocumentAuthor and tblDocumentRecipient tables, so atleast I could use joins rather than subqueries, but I'd prefer to keep it as it is currently as the database may require CC and BCC recipients in the near future. Also is using GROUP_CONCAT the best way to represent a summary of all authors/recipients?

Thanks for helping!

Robert Hofmeyr