Need someone to optimize my sites database and fix it this is my hosting says
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.