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 

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!