2

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
Community
  • 1
  • 1
christian.thomas
  • 1,122
  • 1
  • 8
  • 19
  • 1
    Do you need all of these joins to be left joins? Do you understand the difference between inner and left joins? – user1191247 Mar 27 '12 at 21:14
  • Can you show us some CREATE TABLE statements? What indexes do you have? – Daan Mar 27 '12 at 21:26
  • A product doesn't necessarily need a category, brand, author, narrator or have any link files.. As far as I understand I'd need to use LEFT JOIN's in that case right? Because if I used INNER JOINS it wouldn't match anything on some items? – christian.thomas Mar 27 '12 at 21:28
  • Yes, that is correct. I just wanted to be sure that you knew the difference. Try the query I added below for performance. – user1191247 Mar 27 '12 at 21:41

1 Answers1

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.

user1191247
  • 10,808
  • 2
  • 22
  • 32