title: MariaDB 10.4.13 slow performance compared to MySQL 5.7.30
body: <p>Having trouble with particular query performance <em>after migrating</em> large (3+ GB) database from MySQL data to MariaDB, it is 64bit version. The database is analyzed, optimized, rebuild. Below is the config of MariaDB, database scheme and the query in question. </p>
  
  <p>Greatly appreciate an advise of <strong>what/how/where/when</strong> to approach this problem.</p>
  
  <p>Machine parameters are: Intel Core i5 CPU @3.6GHz, 16GB RAM, Sandisk 512GB SSD, using Windows 10 v.1909.</p>
  
  <p>SQL query with slow performance ( 10 seconds, used to be around 1 second on MySQL 5.7):</p>
  
  <pre><code>SELECT * FROM (
          SELECT 
        '#AT&amp;T' AS instrument,
        (SELECT '2020-05-21 09:30' AS report_period) report_period,
      #Average price
          (SELECT AVG(avg_price.avg_price) AS avg_price FROM 
          (
            SELECT  AVG(t.CLOSE_PRICE) AS avg_price
            FROM mt4_trades t
            WHERE t.CLOSE_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND)  AND t.OPEN_TIME &gt; '2012-08-26' 
            AND t.SYMBOL LIKE '#AT&amp;T%' AND t.CMD IN (0,1) 
          UNION ALL
            SELECT  AVG(t.OPEN_PRICE) AS avg_price
            FROM mt4_trades t
            WHERE t.OPEN_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) 
            AND t.SYMBOL LIKE '#AT&amp;T%' AND t.CMD IN (0,1)  
  
          ) avg_price) avg_price,          
  
        #Total deals value
          (
          SELECT SUM(total_deals_value.total_deals_value) AS total_deals_value FROM   (
            SELECT SUM(t.VOLUME/100.0 * 1  * t.CLOSE_PRICE ) AS total_deals_value
            FROM mt4_trades t
            WHERE t.CLOSE_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) AND t.OPEN_TIME &gt; '2012-08-26' 
            AND t.SYMBOL LIKE '#AT&amp;T%'  AND t.CMD IN (0,1) 
          UNION ALL           
            SELECT SUM(t.VOLUME/100.0 * 1  * t.OPEN_PRICE ) AS total_deals_value      
            FROM mt4_trades t
            WHERE t.OPEN_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND)   
            AND t.SYMBOL LIKE '#AT&amp;T%'  AND t.CMD IN (0,1)  
            ) total_deals_value) AS total_deals_value) result
  
          LEFT OUTER JOIN   
          (SELECT '#AT&amp;T' AS instrument, @fd_time0 AS fd_time, @fd_price0 AS fd_price, 
            (@fd_volume0/100.0 * 1  * @fd_price0 ) AS fd_volume 
              FROM (
                SELECT @fd_time0 := fd_time AS fd_time, @fd_volume0 := VOLUME AS VOLUME, @fd_price0 := PRICE AS PRICE 
                FROM 
                    (SELECT MIN(t.CLOSE_TIME) AS fd_time, t.VOLUME, t.CLOSE_PRICE AS PRICE FROM mt4_trades t WHERE t.CLOSE_TIME BETWEEN 
                      DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) AND '2020-05-21 11:30' AND t.OPEN_TIME &gt; '2012-08-26' 
                      AND t.SYMBOL LIKE '#AT&amp;T%' 
                    UNION ALL
                    SELECT MIN(t.OPEN_TIME) AS fd_time, t.VOLUME, t.OPEN_PRICE AS PRICE FROM mt4_trades t WHERE t.OPEN_TIME BETWEEN 
                      DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) AND '2020-05-21 11:30' 
                      AND t.SYMBOL LIKE '#AT&amp;T%'   
                      ORDER BY fd_time) first_deal WHERE first_deal.fd_time IS NOT NULL ORDER BY first_deal.fd_time ASC LIMIT 1
              ) AS first_deal) temp_result ON temp_result.instrument =  result.instrument 
  </code></pre>
  
  <p>Explain of the SQL query:
  <a href="https://i.stack.imgur.com/7EYxW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7EYxW.png" alt="enter image description here"></a></p>
  
  <p>Create SQL for table:</p>
  
  <pre><code>CREATE TABLE `mt4_trades` (
  `TICKET` INT(11) UNSIGNED NOT NULL,
  `LOGIN` INT(11) UNSIGNED NOT NULL,
  `SYMBOL` VARCHAR(16) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
  `DIGITS` TINYINT(3) UNSIGNED NOT NULL,
  `CMD` TINYINT(3) UNSIGNED NOT NULL,
  `VOLUME` MEDIUMINT(8) UNSIGNED NOT NULL,
  `OPEN_TIME` DATETIME NOT NULL,
  `OPEN_PRICE` FLOAT(12,0) NOT NULL,
  `SL` FLOAT(12,0) NOT NULL,
  `TP` FLOAT(12,0) NOT NULL,
  `CLOSE_TIME` DATETIME NOT NULL,
  `EXPIRATION` DATETIME NOT NULL,
  `REASON` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
  `CONV_RATE1` FLOAT(12,0) NOT NULL,
  `CONV_RATE2` FLOAT(12,0) NOT NULL,
  `COMMISSION` FLOAT(12,0) NOT NULL,
  `COMMISSION_AGENT` FLOAT(12,0) NOT NULL,
  `SWAPS` FLOAT(12,0) NOT NULL,
  `CLOSE_PRICE` FLOAT(12,0) NOT NULL,
  `PROFIT` FLOAT(12,0) NOT NULL,
  `TAXES` FLOAT(12,0) NOT NULL,
  `COMMENT` VARCHAR(32) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
  `INTERNAL_ID` INT(11) NOT NULL,
  `MARGIN_RATE` FLOAT(12,0) NOT NULL,
  `TIMESTAMP` INT(11) UNSIGNED NOT NULL,
  `MAGIC` INT(11) NOT NULL DEFAULT '0',
  `GW_VOLUME` INT(11) NOT NULL DEFAULT '0',
  `GW_OPEN_PRICE` INT(11) NOT NULL DEFAULT '0',
  `GW_CLOSE_PRICE` INT(11) NOT NULL DEFAULT '0',
  `MODIFY_TIME` DATETIME NOT NULL,
  PRIMARY KEY (`TICKET`) USING BTREE,
  INDEX `INDEX_STAMP` (`TIMESTAMP`, `COMMENT`) USING BTREE,
  INDEX `CMD` (`CMD`, `OPEN_TIME`, `CLOSE_TIME`, `LOGIN`, `VOLUME`, `SYMBOL`, `CLOSE_PRICE`) USING 
  BTREE
  )
  COLLATE='utf8_general_ci'
  ;
  </code></pre>
  
  <p>MariaDB's <code>my.ini</code></p>
  
  <pre><code>[mysqld]
  port= 3306
  socket = "C:/xampp/mysql/mysql.sock"
  basedir = "C:/xampp/mysql" 
  tmpdir = "C:/xampp/tmp" 
  datadir = "C:/xampp/mysql/data"
  log_error = "mysql_error.log"
  pid_file = "mysql.pid"
  collation_server=utf8_general_ci
  character_set_server=utf8
  
  ## CUSTOM EDIT
  sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,STRICT_TRANS_TABLES
  skip_external_locking
  skip_name_resolve
  max_connections                 = 200
  table_open_cache                = 10000
  table_definition_cache          = 2000
  open_files_limit                = 20000
  ##MyISAM setting
  key_buffer                      = 512M
  myisam_sort_buffer_size         = 2M
  #
  max_allowed_packet              = 16M
  max_sort_length                 = 16384
  sort_buffer_size                = 1M
  net_buffer_length               = 64K
  read_buffer_size                = 256K
  read_rnd_buffer_size            = 512K
  #INNO DB settings
  innodb_file_per_table           = 1
  innodb_buffer_pool_size         = 4G
  innodb_sort_buffer_size         = 16M
  ## Set .._log_file_size to 25 % of buffer pool size
  innodb_log_file_size            = 1024M
  innodb_log_buffer_size          = 32M
  innodb_flush_log_at_trx_commit  = 2
  innodb_stats_on_metadata        = 0
  innodb_lock_wait_timeout        = 600
  innodb_flush_method             = normal
  #A minor optimization when writing blocks to disk. Use 0 for SSD drives; 1 for HDD.
  innodb_flush_neighbors          = 0
  innodb_io_capacity              = 2000
  #
  innodb_buffer_pool_instances    = 3
  innodb_thread_concurrency       = 12
  innodb_autoextend_increment     = 64
  innodb_read_io_threads          = 16
  innodb_write_io_threads         = 16
  concurrent_insert               = 2
  thread_stack                    = 512K
  interactive_timeout             = 600
  wait_timeout                    = 600
  query_cache_type                = 2
  query_cache_limit               = 64M
  query_cache_min_res_unit        = 1
  query_cache_size                = 16M
  thread_cache_size               = 128
  low_priority_updates
  tmp_table_size                  = 4M
  max_heap_table_size             = 4M
  bulk_insert_buffer_size         = 256M
  group_concat_max_len            = 512K
  # Define which query should be considered as slow, in seconds
  long_query_time                 = 6
  join_cache_level                = 8
  # Size limit for the whole join
  #join_buffer_space_limit        = 512M
  join_buffer_size                = 4M
  # Optimizer switches
  optimizer_switch                ='orderby_uses_equalities=on'
  optimizer_switch                ='mrr=on,mrr_sort_keys=on'
  optimizer_switch                ='index_merge_sort_intersection=on'
  optimizer_switch                ='optimize_join_buffer_size=on'
  optimizer_switch                ='join_cache_bka=on'
  optimizer_switch                ='join_cache_hashed=on'
  optimizer_switch='in_to_exists=on'
  optimizer_switch='join_cache_incremental=on'
  #optimizer_switch='loosescan=on'
  
  # Where do all the plugins live
  plugin_dir = "C:/xampp/mysql/lib/plugin/" 
  server-id   = 1
  </code></pre>