Fix database problem | Bitcointalksearch.org

altcoinhosting

hero member

Activity: 896

Merit: 1006

Quote from: Joel_Jantsen on April 29, 2016, 01:57:58 AM

Quote from: altcoinhosting on April 29, 2016, 01:34:31 AM

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.

Joel_Jantsen

legendary

Activity: 1988

Merit: 1317

Get your game girl

Quote from: altcoinhosting on April 29, 2016, 01:34:31 AM

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

hero member

Activity: 896

Merit: 1006

Quote from: elobizph on April 29, 2016, 01:39:05 AM

Quote from: altcoinhosting on April 29, 2016, 01:34:31 AM

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.

elobizph

full member

Activity: 168

Merit: 100

Quote from: altcoinhosting on April 29, 2016, 01:34:31 AM

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

hero member

Activity: 896

Merit: 1006

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

full member

Activity: 168

Merit: 100

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

Topic: Fix database problem (Read 329 times)