Slow mysql performance

Question

few days ago my mysql is very slow and some sql queries don't work anymore, I dont know from where come this problem.

OS: Centos

MySQL: 5.7 - InnoDB

MySQL -V: mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper

Its my query

SELECT 
  BTCountry.id,
  BTCountry.name,
  BTCountry.code,
  BTCountry.last_data_from,
  BTCountry.year_last_data_from AS current_year,
  (SELECT 
    SUM(cantidad) AS subCount 
  FROM
    `krakente_marketpins`.`bt_countries` AS `subBTCountry` 
    INNER JOIN `krakente_marketpins`.`bus_trucks` AS `subBusTruck` 
      ON (
        `subBusTruck`.`pais_mercado_id` = `subBTCountry`.`id`
      ) 
  WHERE `subBusTruck`.`pais_mercado_id` = `BTCountry`.`id` 
    AND `subBusTruck`.`year_data_from` = 
      `subBTCountry`.`year_last_data_from`

    AND `subBusTruck`.month_data_from <= `subBTCountry`.`month_last_data_from`) AS ytd,
  (SELECT 
    SUM(cantidad) AS subCount 
  FROM
    `krakente_marketpins`.`bt_countries` AS `subBTCountry` 
    INNER JOIN `krakente_marketpins`.`bus_trucks` AS `subBusTruck` 
      ON (
        `subBusTruck`.`pais_mercado_id` = `subBTCountry`.`id`
      ) 
  WHERE `subBusTruck`.`pais_mercado_id` = `BTCountry`.`id` 
    AND `subBusTruck`.`year_data_from` = `subBTCountry`.`year_last_data_from` - 1 
    AND `subBusTruck`.`month_data_from` <= `subBTCountry`.`month_last_data_from`) AS last_year 
FROM
  bt_countries AS BTCountry 
  INNER JOIN `krakente_marketpins`.`bus_trucks` AS `BusTruck` 
    ON (
      `BusTruck`.`pais_mercado_id` = `BTCountry`.`id`
    ) 
WHERE `pais_mercado_id` IN (
    '1',
    '2',
    '3',
    '4',
    '5',
    '6',
    '7',
    '8',
    '9',
    '10',
    '11',
    '12'
  ) 
  AND mercado_id = ('1') 
GROUP BY `BTCountry`.`id` 
ORDER BY `ytd` DESC 

I know this query can be optimized but i need know why my mysql is working bad now.

I used mysqltuner to get recommendations but without luck. its my tunner log

Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    table_open_cache (> 1024)
    table_definition_cache(1024) > 5155 or -1 (autosizing if supported)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

How I said before, the query work few days ago but suddenly this no work anymore.

in other hand i have multiple wp sites and these sites suddenly come very very slow and I think it come from mysql also, because if I enter to a site without sql the site work normal.

Where I can start to check?

EDIT

its explain query

explain query

Answer 1

This is pretty much pseudo-code, however, I bet you can do the aggregation while passing over the data only once. Since you already have the data grouped, you don't need to subquery two more times in your select.

SELECT 
  BTCountry.id,
  BTCountry.name,
  BTCountry.code,
  BTCountry.last_data_from,
  BTCountry.year_last_data_from AS current_year,
  ytd =         SUM(CASE WHEN BusTruck.year_data_from = bt_countries.year_last_data_from - 1 AND bus_trucks.month_data_from <= bt_countries.month_last_data_from THEN cantidad ELSE NULL END),
  last_year =   SUM(CASE WHEN BusTruck.year_data_from = bt_countries.year_last_data_from AND bus_trucks.month_data_from <= bt_countries.month_last_data_from THEN cantidad ELSE NULL END)
FROM
  bt_countries AS BTCountry 
  INNER JOIN krakente_marketpins.bus_trucks AS BusTruck ON BusTruck.pais_mercado_id =BTCountry.id
WHERE
    pais_mercado_id IN (
    '1',
    '2',
    '3',
    '4',
    '5',
    '6',
    '7',
    '8',
    '9',
    '10',
    '11',
    '12'
  ) 
  AND mercado_id = ('1') 
GROUP 
    BYBTCountry.id,
    BTCountry.name,
    BTCountry.code,
    BTCountry.last_data_from,
    BTCountry.year_last_data_from
ORDER 
    BYytd 
DESC