Таймаут при выполнении SQL запроса к БД

153

22 февраля 2018, 11:47

Есть работающий запрос, индексы созданы, выполняется в Navicat 290+- секунд, но при выполнении его из своего софта получаю ошибку:

Db.ExecuteQuery Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Код запроса:

SELECT
`coins`.`name` AS `Монета`,
`coins`.`symbol` AS `Код`,
round(
avg(
( CASE WHEN ( `history`.`date_load_stats` > ( now( ) + INTERVAL - ( 24 ) HOUR ) ) THEN `history`.`difficulty` END ) 
),
2 
) AS `AVG diff 24ч`,
round( `coins`.`difficulty`, 2 ) AS `Diff`,
round( `coins`.`coin_reward_day`, 3 ) AS `coin/day`,
round( `coins`.`price_usd`, 4 ) AS `Цена, $`,
round( `coins`.`coin_reward_day_usd`, 2 ) AS `$/day`,
format( `coins`.`price_btc`, 8 ) AS `Цена, BTC`,
`coins`.`coin_reward_day_btc` AS `BTC/day`,
round( `coins`.`coin_reward_month_usd`, 0 ) AS `$/мес`,
round( `coins`.`coin_reward_month_btc`, 3 ) AS `BTC/мес`,
`coins`.`algo` AS `algo`,
round( ( `hashrate`.`hashrate` * 8 ), 2 ) AS `8 GPU hash`,
`unit`.`unit` AS `unit`,
round( `coins`.`block_reward`, 3 ) AS `Block reward`,
`coins`.`percent_change_1h` AS `Час, %`,
`coins`.`percent_change_24h` AS `Day, %`,
`coins`.`percent_change_7d` AS `Week, %`,
`coins`.`url_curr_loaded` AS `URL стоимости`,
`coins`.`url_diff_loaded` AS `URL сложности`,
`coins`.`date_last_update` AS `Price update`,
`coins`.`date_last_load_diff` AS `Diff update`,
`coins`.`count_calculated_profit` AS `Расчетов`,
`coins`.`url_wtm` AS `url_wtm`,
`coins`.`announcement` AS `Анонс`,
`coins`.`url_on_coinmarketcap` AS `url_on_coinmarketcap`,
`coins`.`start_date` AS `Дата запуска`,
`coins`.`wallet_status` AS `wallet_status`,
`hashrate`.`miner` AS `miner`,
`hashrate`.`intensity` AS `intensity` 
FROM
    (
    `history`
    JOIN (
    `coins`
    JOIN (
    `unit`
    JOIN (
    `gpu`
    JOIN ( `algo` JOIN `hashrate` ON ( ( `algo`.`Код` = `hashrate`.`algo` ) ) ) ON ( ( `gpu`.`Код` = `hashrate`.`gpu` ) ) 
    ) ON ( ( `unit`.`Код` = `hashrate`.`unit` ) ) 
    ) ON ( ( `coins`.`algo` = `algo`.`algo_name` ) ) 
    ) ON ( ( `history`.`coin_name` = `coins`.`name` ) ) 
    ) 
WHERE
    ( `coins`.`scam_or_token_coin` = 0 ) 
GROUP BY
    `coins`.`name`,
    `coins`.`symbol`,
    `coins`.`count_calculated_profit`,
    `coins`.`start_date`,
    round( `coins`.`difficulty`, 2 ),
    round( `coins`.`coin_reward_day`, 3 ),
    round( `coins`.`price_usd`, 4 ),
    `coins`.`coin_reward_day_usd`,
    `coins`.`price_btc`,
    `coins`.`coin_reward_day_btc`,
    round( `coins`.`coin_reward_month_usd`, 2 ),
    `coins`.`coin_reward_month_btc`,
    `coins`.`url_wtm`,
    ( `hashrate`.`hashrate` * 8 ),
    `coins`.`algo`,
    round( `coins`.`block_reward`, 3 ),
    `coins`.`percent_change_1h`,
    `coins`.`percent_change_24h`,
    `coins`.`percent_change_7d`,
    `coins`.`url_curr_loaded`,
    `coins`.`url_diff_loaded`,
    `coins`.`date_last_update`,
    `coins`.`date_last_load_diff`,
    `coins`.`announcement`,
    `coins`.`url_on_coinmarketcap`,
    `coins`.`wallet_status`,
    `coins`.`url_explorer_1`,
    `hashrate`.`miner`,
    `hashrate`.`intensity`,
    `coins`.`block_time` 
HAVING
    (
    (
    ( `coins`.`coin_reward_day_usd` > 8 ) 
    AND ( `coins`.`date_last_update` > ( now( ) + INTERVAL - ( 3 ) HOUR ) ) 
    AND ( `coins`.`date_last_load_diff` > ( now( ) + INTERVAL - ( 3 ) HOUR ) ) 
    ) 
    OR (
    ( `coins`.`coin_reward_day_btc` > 0.001 ) 
    AND ( `coins`.`date_last_update` > ( now( ) + INTERVAL - ( 3 ) HOUR ) ) 
    AND ( `coins`.`date_last_load_diff` > ( now( ) + INTERVAL - ( 3 ) HOUR ) ) 
    ) 
    ) 
ORDER BY
    `coins`.`coin_reward_day_btc` DESC

Как можно его оптимизировать по времени работы? Либо может что-то еще добавить в БД для увеличения скорости? Внешние ключи не прописаны, только индексы по всем более-менее статичным полям.

UPD: Переделал по советам в комментариях запрос в такой вид:

  SELECT
    coins.`name` AS 'Монета',
    coins.symbol AS 'Код',
    Round(
    Avg( CASE WHEN history.date_load_stats > DATE_ADD( NOW( ), INTERVAL - 24 HOUR ) THEN history.difficulty END ),
    2 
    ) AS 'AVG diff 24ч',
    Round( coins.difficulty, 2 ) AS 'Diff',
    Round( coins.coin_reward_day, 3 ) AS 'coin/day',
    Round( coins.price_usd, 4 ) AS 'Цена, $',
    Round( coins.coin_reward_day_usd, 2 ) AS '$/day',
    coins.price_btc AS 'Цена, BTC',
    coins.coin_reward_day_btc AS 'BTC/day',
    Round( coin_reward_month_usd, 2 ) AS '$/мес',
    coins.coin_reward_month_btc AS 'BTC/мес',
    coins.algo,
    hashrate.hashrate * 8 AS '8 GPU hash',
    unit.unit,
    Round( coins.block_reward, 3 ) AS 'Block reward',
    coins.percent_change_1h AS 'Час, %',
    coins.percent_change_24h AS 'Day, %',
    coins.percent_change_7d AS 'Week, %',
    coins.url_curr_loaded AS 'URL стоимости',
    coins.url_diff_loaded AS 'URL сложности',
    coins.date_last_update AS 'Price update',
    coins.date_last_load_diff AS 'Diff update',
    coins.count_calculated_profit AS 'Расчетов',
    coins.url_wtm,
    coins.announcement AS 'Анонс',
    coins.url_on_coinmarketcap,
    coins.start_date AS 'Дата запуска',
    coins.wallet_status,
    hashrate.miner,
    hashrate.intensity 
FROM
    history,
    coins,
    unit,
    algo,
    hashrate 
WHERE
    ( coins.scam_or_token_coin = 0 ) 
    AND (
    ( ( coins.date_last_update ) > DATE_ADD( NOW( ), INTERVAL - 3 HOUR ) ) 
    AND ( ( coins.date_last_load_diff ) > DATE_ADD( NOW( ), INTERVAL - 3 HOUR ) ) 
    ) 
    AND ( ( ( coins.coin_reward_day_usd ) > 8 ) OR ( ( coins.coin_reward_day_btc ) > 0.001 ) ) 
    AND (
    algo.`Код` = hashrate.algo 
    AND unit.`Код` = hashrate.unit 
    AND coins.algo = algo.algo_name 
    AND history.coin_name = coins.`name` 
    ) 
GROUP BY
    coins.`name` 
ORDER BY
    coins.coin_reward_day_btc DESC;