1
\$\begingroup\$

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.

\$\endgroup\$
0

1 Answer 1

5
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ Works, though I don't like your tiny, non-descriptive table aliases. Still, looks more efficient. +1 \$\endgroup\$
    – Phrancis
    Commented Sep 19, 2014 at 2:07
  • \$\begingroup\$ @Kickstart sorry for not providing more details on the table structure, each a person can have multiple emails/phones/addresses and is_primary column identifies which of those emails/phones/addresses is currently in use by that particular person. \$\endgroup\$
    – Jeremy
    Commented Sep 19, 2014 at 2:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.