Fix database problem

[elobizph]

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.

Pm me if you can fix it.and how much

[altcoinhosting]

I'm a DBA, i can have a look at this if you want to... I charge $35/hour for DBA work.
You're only joining 3 tables tough, i find it a bit odd it would take this long, but i can only say for sure after i've inspected your database.

[elobizph]

I'm a DBA, i can have a look at this if you want to... I charge $35/hour for DBA work.
You're only joining 3 tables tough, i find it a bit odd it would take this long, but i can only say for sure after i've inspected your database.

ok I just pmed you and I hope we can fix it as soon as possible.

[altcoinhosting]

I'm a DBA, i can have a look at this if you want to... I charge $35/hour for DBA work.
You're only joining 3 tables tough, i find it a bit odd it would take this long, but i can only say for sure after i've inspected your database.

ok I just pmed you and I hope we can fix it as soon as possible.

BTW: forgot to mention this, about the timing: i have some spare time on my lunchbreak today, and this evening... If your problem is fixable in 1-2 hours, i can do this today, otherwise it'll be tomorrow morning.

[Joel_Jantsen]

I'm a DBA, i can have a look at this if you want to... I charge $35/hour for DBA work.
You're only joining 3 tables tough, i find it a bit odd it would take this long, but i can only say for sure after i've inspected your database.

$35 an hour ? lol .OP the problem doesn't seem to be that tough neither the query is complex.I would fix the issue for $10 and provide you the most optimal run time of it.Unfortunately I don't have time on me but I will try and send you a fix maybe tomorrow.

If you wish to save your $35 ,go to freelancer.in ,any Indian will get it done within seconds for as cheap as $5.

[altcoinhosting]

I'm a DBA, i can have a look at this if you want to... I charge $35/hour for DBA work.
You're only joining 3 tables tough, i find it a bit odd it would take this long, but i can only say for sure after i've inspected your database.

$35 an hour ? lol .OP the problem doesn't seem to be that tough neither the query is complex.I would fix the issue for $10 and provide you the most optimal run time of it.Unfortunately I don't have time on me but I will try and send you a fix maybe tomorrow.

If you wish to save your $35 ,go to freelancer.in ,any Indian will get it done within seconds for as cheap as $5.

I'm sorry you feel the need to ridicule $35/hour, this is the common rate for dba work in my country.
I haven't analysed the problem yet, but if you offer to do it for $10, the OP is still free to chose you.