title: MySQL SUM Query is extremely slow
body: <p>There is a table called <code>transactions</code> with ~6 million rows. Below query counts the current user balance. Here is the log after I enabled <code>slow_query_log = 'ON'</code> :</p>
  
  <pre><code># Time: 170406  9:51:48
  # User@Host: root[root] @  [xx.xx.xx.xx]
  # Thread_id: 13  Schema: main_db  QC_hit: No
  # Query_time: 38.924823  Lock_time: 0.000034  Rows_sent: 1  Rows_examined: 773550
  # Rows_affected: 0
  SET timestamp=1491456108;
  SELECT SUM(`Transaction`.`amount`) as total
      FROM `main_db`.`transactions` AS `Transaction`
      WHERE `Transaction`.`user_id` = 1008
        AND `Transaction`.`confirmed` = 1
      LIMIT 1;
  </code></pre>
  
  <p>As you can see it took <code>~38 seconds</code> !</p>
  
  <p>Here is <code>transactions</code> table EXPLAIN :</p>
  
  <p><a href="https://i.stack.imgur.com/bKCXe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bKCXe.png" alt="enter image description here"></a></p>
  
  <p>This query sometimes run fast (about ~1 second) and sometimes really slow!</p>
  
  <p>Any help would be great appreciated.</p>
  
  <p><strong>P.S:</strong></p>
  
  <p>It's InnoDB and <code>transactions</code> table has frequent INSERT and SELECT operations.</p>
  
  <p>I tried running the query with <code>SQL_NO_CACHE</code>, but it is still sometimes fast, sometimes slow.</p>
  
  <p><code>transactions</code> Table Schema :</p>
  
  <pre><code>CREATE TABLE `transactions` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(10) unsigned NOT NULL,
    `ref_id` varchar(40) COLLATE utf8_persian_ci NOT NULL,
    `payment_id` tinyint(3) unsigned NOT NULL,
    `amount` decimal(10,1) NOT NULL,
    `created` datetime NOT NULL,
    `private_note` varchar(6000) COLLATE utf8_persian_ci NOT NULL,
    `public_note` varchar(200) COLLATE utf8_persian_ci NOT NULL,
    `confirmed` tinyint(3) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=13133663 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci
  </code></pre>
  
  <p>MySQL is running on a VPS with 12GB RAM and 9 Logical CPU cores. </p>
  
  <p>Here is a part of <code>my.cnf</code> :</p>
  
  <pre><code># * InnoDB
  #
  # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
  # Read the manual for more InnoDB related options. There are many!
  default_storage_engine  = InnoDB
  # you can't just change log file size, requires special procedure
  innodb_buffer_pool_size = 9G
  innodb_log_buffer_size  = 8M
  innodb_file_per_table   = 1
  innodb_open_files       = 400
  innodb_io_capacity      = 400
  innodb_flush_method     = O_DIRECT
  innodb_thread_concurrency = 0
  innodb_read_io_threads = 64
  innodb_write_io_threads = 64
  
  
  # Instead of skip-networking the default is now to listen only on
  # localhost which is more compatible and is not less secure.
  #bind-address           = 127.0.0.1
  #
  # * Fine Tuning
  #
  max_connections         = 500
  connect_timeout         = 5
  wait_timeout            = 600
  max_allowed_packet      = 16M
  thread_cache_size       = 128
  sort_buffer_size        = 4M
  bulk_insert_buffer_size = 16M
  tmp_table_size          = 32M
  max_heap_table_size     = 32M
  </code></pre>