Why does a MySQL 'where' clause with Index make a SUM query slower?

Question

I have a table with 5 million records. Table columns are id, customer_id, status, and amount. When I run a query without Index(status),

SELECT SUM(amount) FROM `game_results` WHERE `status` = 1

the query takes 4 seconds to complete, but when I add an index to status column, the query takes 45 seconds to complete. I need Index for the status column, because I also need to make search queries on this table.

How can I solve this issue?

Slow query with index - EXPLAIN

Slow query with index - ANALYZE

Query without index - EXPLAIN

Query without index - ANALYZE

customer_id, game_id, date, prize_type filterable.

amount, point columns for summary stats.

Show Create Table

CREATE TABLE `game_results`(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) NOT NULL,
`game_id` bigint(20) NOT NULL,
`amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`prize_id` smallint(6) NOT NULL DEFAULT '0',
`prize_type` smallint(6) NOT NULL DEFAULT '1',
`point` decimal(10,2) NOT NULL DEFAULT '0.00',
`ext_id` bigint(20) DEFAULT NULL,
`ext_value` bigint(20) DEFAULT NULL,
`status` smallint(6) NOT NULL DEFAULT '0',
`site_user` bigint(20) NOT NULL DEFAULT '0',
`date` bigint(20) NOT NULL DEFAULT '0',
`update_date` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10009524
   DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

Rolando's composite index is the "perfect" solution to the question.

Or is it?

Sure, it made that one query run 4 times as fast. I would argue that 1 second is still "too long". To discuss that, we need to understand "why" that query is necessary, and how often it is run.

Also, let's look at the side effects of having that index. The column names (status and amount) smell like columns that will be changed often. If so, then be aware that updating INDEX(status, amount) is like deleting a row from one spot a BTree and inserting another row in another spot. Granted, InnoDB does a good job of making those two steps reasonably efficient (cf "change buffering"), but it is not free. This overhead occurs after the UPDATE statement that modifies status and/or amount. It is probably not a big deal in the grand scheme of things.

Without further information about the app, I cannot necessarily recommend a "summary table" or a "trigger" as a way to decrease the 1 second down to under 0.1 seconds.

For "a list of all game results belongs to user 1", you probably need an INDEX starting with user_id.

For "a summary of last month's game amounts", and there is no other filtering, an index starting with date may be useful.

I see "cardinality = 1" for status. This implies that the Optimizer thinks there is only one distinct value for status. If this is the case, the Optimizer may do things differently. (And certainly it is useless to have INDEX(status) for the query you gave when you are testing for that value.)

Answer 2

The reason the status index makes it go slower is this:

• The value of status is read from the index
• The value of amount must be looked up in the table

This means having to read from the index and the table separately per index entry. In your case, doing a full table scan, where both the status and amount values are, would be better without using an index. What you need is a good covering index, an index with all the columns needed for a specific query.

Drop the status index and add a compound index on status,amount:

ALTER TABLE game_results
    DROP INDEX status
   ,ADD INDEX status_amount_ndx (status,amount)
   ,ALGORITHM=INPLACE
   ,LOCK=NONE
;

The query will use this new index, status_amount_ndx, to get both status and amount values without having to read from the table.

Give it a try!!!