MySQL Query Optimisation

Question

Looking for some help with optimising the query below. Seems to be two bottlenecks at the moment which cause it to take around 90s to complete the query. There's only 5000 products so it's not exactly a massive database/table. The bottlenecks are SQL_CALC_FOUND_ROWS and the ORDER BY statement - If I remove both of these it takes around a second to run the query. I've tried removing SQL_CALC_FOUND_ROWS and running a count() statement, but that takes a long time as well..

Is the best thing going to be to use INNER JOIN's (which I'm not too familiar with) as per the following Stackoverflow post? Slow query when using ORDER BY

SELECT SQL_CALC_FOUND_ROWS * 
FROM tbl_products
LEFT JOIN tbl_link_products_categories ON lpc_p_id = p_id
LEFT JOIN tbl_link_products_brands ON lpb_p_id = p_id
LEFT JOIN tbl_link_products_authors ON lpa_p_id = p_id
LEFT JOIN tbl_link_products_narrators ON lpn_p_id = p_id
LEFT JOIN tbl_linkfiles ON lf_id = p_id
AND (
lf_table = 'tbl_products'
OR lf_table IS NULL
)
LEFT JOIN tbl_files ON lf_file_id = file_id
AND (
file_nameid = 'p_main_image_'
OR file_nameid IS NULL
)
WHERE p_live = 'y'
ORDER BY p_title_clean ASC, p_title ASC
LIMIT 0 , 10

Answer 1

You could try reducing the size of the joins by using a derived table to retrieve the filtered and ordered products before joining. This assumes that p_live, p_title_clean and p_title are fields in your tbl_products table -

SELECT * 
FROM (SELECT * 
    FROM tbl_products
    WHERE p_live = 'y'
    ORDER BY p_title_clean ASC, p_title ASC
    LIMIT 0 , 10
) AS tbl_products
LEFT JOIN tbl_link_products_categories
    ON lpc_p_id = p_id
LEFT JOIN tbl_link_products_brands
    ON lpb_p_id = p_id
LEFT JOIN tbl_link_products_authors
    ON lpa_p_id = p_id
LEFT JOIN tbl_link_products_narrators
    ON lpn_p_id = p_id
LEFT JOIN tbl_linkfiles
    ON lf_id = p_id
    AND (
        lf_table = 'tbl_products'
        OR lf_table IS NULL
    )
LEFT JOIN tbl_files
    ON lf_file_id = file_id
    AND (
        file_nameid = 'p_main_image_'
        OR file_nameid IS NULL
    )

This is a "stab in the dark" as there is not enough detail in your question.