March 31, 2013
How can we speed up the query*
Question by mustafa
I have written this code to query the mysql tables customerselections, companies and companycampaigns. The tables have indexes on necessary fields.
The query time (without caching) is around 0,30. I think query time is high considering concurrent connections. Is it possible to rewrite in a different way to speed up the query time?
SELECT
customerselections.customer_id,
customerselections.selectedcompany_id,
customerselections.selection_id,
companycampaigns.campaign_id,
companycampaigns.company_id,
companycampaigns.campaign_title,
companycampaigns.campaign_detail,
companycampaigns.published,
companies.company_logo,
companies.company_gsm,
companies.company_landline,
companies.company_address
FROM customerselections
LEFT JOIN companies ON customerselections.selectedcompany_id=companies.company_id
LEFT JOIN companycampaigns ON companycampaigns.company_id=companies.company_id AND companycampaigns.published='1'
WHERE customerselections.customer_id='$customerid' LIMIT $offset,$limit
Answer by Starx
Select based on field like customerselections.customer_id is slower comparing to selecting all rows as server does not have to do any filtering. So this will give a minor boost.
SELECT
customerselections.*,
companycampaigns.*,
companies.*
FROM customerselections
LEFT JOIN companies ON customerselections.selectedcompany_id=companies.company_id
LEFT JOIN companycampaigns ON companycampaigns.company_id=companies.company_id AND companycampaigns.published='1'
WHERE customerselections.customer_id='$customerid' LIMIT $offset,$limit
