So my hosting says I need to fix this issue to optimize my sites performance
Here is the join query which causes significant performance issues and should be optimized or re-written and split into few simple queries:
select *, count(distinct views.views_id) as ViewCount from campaigns left join views ON views.views_campaign_id = campaigns.campaigns_id left join rates on rates.rates_id = campaigns.campaigns_rates_id where campaigns.campaigns_budget_used_today < campaigns.campaigns_daily_budget and campaigns.campaigns_current_budget > 0 and campaigns.campaigns_approved = 1 and campaigns.campaigns_status = 0 and campaigns.campaigns_payment_accepted = 1 and campaigns_device = 1 or campaigns.campaigns_device = 0 and rates_location = '' or rates.rates_location IS NULL group by campaigns.campaigns_daily_budget having ViewCount <= campaigns.campaigns_ordered_views * 1000 order by campaigns.campaigns_daily_budget asc
It is a complex query with joins, which does not use indexes for some of the used columns. As the result, MySQL server creates temporary tables, performs scan of lots of table rows, and takes seconds to complete, which is not normal.
We have added indexes to the tables and columns used in the query. However it can not fix the issue completely, and the query still takes a few seconds. The solution would be to rewrite the query, or replace it with a few simple select queries which use indexes.
Hi there,
I am a currently studying Software Engineering in Pakistan.
I know about SQL and MYSQL.
But
Right now i could not understand your query without seeing the entire Database and table.
Kindly Sent me your entire database so i can find errors
Please inbox me if you are willing to work with me.
Waiting for your response.
Thanks
Kind Regards
Web&GraphicExpert
$15 USD in 1 day
0.0 (0 reviews)
0.0
0.0
4 freelancers are bidding on average $36 USD for this job
Experienced programmer with more than 10 years of development experience here. I have handled production applications with various technologies like .NET, PHP, Classic ASP and many more using databases like MySQL, Microsoft SQL, Access and etc. Mentioned below are a few USPs for choosing me:
1. Well written codes with proper documentation
2. Highest possible code optimization
3. Quick support through email / skype / telephone
4. Daily / Weekly status reporting
Let's solve your issue.
Regards,
Hardik Shah