MySQL proliferation of subqueries and left joins

Question

I have the following query:

 $sql = "SELECT p.id, p.fname, p.mname, p.surname, p.consultant_id,
                e.address,
                ph.number, 
                a.street, a.street_more, a.city, a.country,
                co.label as 'nationality',
                msap.label as 'module'
        FROM per_person AS p
        LEFT JOIN (
            SELECT person_id, number
            FROM per_phone
            WHERE is_primary = 1
        ) AS ph ON p.id = ph.person_id
        LEFT JOIN (
            SELECT person_id, address
            FROM per_email
            WHERE is_primary = 1
        ) AS e ON p.id = e.person_id
        LEFT JOIN (
            SELECT person_id, street, street_more, city, country
            FROM per_address
            WHERE is_primary = 1
        ) AS a ON p.id = a.person_id
        LEFT JOIN (
            SELECT person_id, module
            FROM per_sap
        ) AS sap ON p.id = sap.person_id
        LEFT JOIN (
            SELECT id as 'country_id', iso_code, label
            FROM meta_country
        ) AS co ON p.nationality = co.country_id
        LEFT JOIN (
            SELECT id as 'sap_id', short_code, label
            FROM meta_sap
        ) AS msap ON sap.module = msap.sap_id
        WHERE is_deleted IS NULL
        ORDER BY fname ASC
        LIMIT ".$index.", ".$per_page.";";

At the moment it takes about 0.078 sec / 0.000 sec for the query to complete (it is limited to take 10 records for each query call).

How would you recommend me to better structure my query so that the execution time can be shortened. Each of those table have (on average) ~ 2000 rows. And all of them are indexed based on the primary_key (id of the table) and FK of the referenced table.

Answer 1

Quick look at this and you have a lot of sub queries that seem unnecessary and could just be rewritten as normal joins. If the is_primary field is added to the foreign keys then this would enable the individual records to be accessed with an index.

Something like this:-

$sql = "SELECT p.id, p.fname, p.mname, p.surname, p.consultant_id,
                e.address,
                ph.number, 
                a.street, a.street_more, a.city, a.country,
                co.label as 'nationality',
                msap.label as 'module'
        FROM per_person AS p
        LEFT JOIN per_phone ph ON p.id = ph.person_id AND ph.is_primary = 1
        LEFT JOIN per_email e ON p.id = e.person_id AND e.is_primary = 1
        LEFT JOIN per_address a ON p.id = a.person_id AND a.is_primary = 1
        LEFT JOIN per_sap sap ON p.id = sap.person_id
        LEFT JOIN meta_country co ON p.nationality = co.country_id
        LEFT JOIN meta_sap msap ON sap.module = msap.sap_id
        WHERE p.is_deleted IS NULL
        ORDER BY p.fname ASC
        LIMIT ".$index.", ".$per_page.";";

Note an index on fname might well help to order the results.