Index Design going incorrect

Question

We have a table called Feedback as follows :

id            - int(10) unsigned    NO  PRI     auto_increment
vendor_Id     - int(10) unsigned    YES MUL     
created_At    - datetime CURRENT_TIMESTAMP  
updated_At    - datetime(3)         
code          - int(11) YES     
feedback      - varchar(300)    YES

CREATE TABLE `Feedback` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_At` datetime(3) NOT NULL,
  `uniqueKey` varchar(255) DEFAULT NULL,
  `customerContactNumber` varchar(15) DEFAULT NULL,
  `customerName` varchar(30) DEFAULT NULL,
  `code` int(11) DEFAULT NULL,
  `feedback` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `feedback_unique` (`uniqueKey `),
  KEY `codeIndex` (`vendor_Id`,`updated_At`,`code`),
  KEY `feedbackIndex` (`vendor_Id`,`code`,`feedback`,`updated_At`),
  CONSTRAINT `feedback_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and we need to create index for our Feedback Table.

So we added 3 indexes based on fields as part of where clause :

 index1 (vendor_Id, updated_At);
 codeIndex (vendor_Id, updated_At, code)
 feedbackIndex(vendor_Id, code, feedback, updated_At)

Now when we run the following query :

explain  select  'id', 'customerName', 'customerContactNumber', 'feedback', 'updated_At'
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
      and  code >=0
      and  code <= 6
    order by  updated_At DESC
    limit  10 

the mysql output says :

possible_keys : index1,codeIndex,feedbackIndex
keys : feedbackIndex

Am curious to know why the explain statement shows 'feedbackIndex' as the key selected. We were expecting it to select 'codeIndex'

There are other 2 queries :

explain  select  code, count(*) as count
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
    group by  code 

===> shows 'codeIndex' as key in output of explain statement. which is as expected

explain  select  code, count(*) as count
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
      and  feedback is not null
    group by  code` 

===> shows 'feedbackIndex' as key in output of explain statement. which is as expected

I can not figure out why our first query is showing feedbackIndex as the keys.

Answer 1

When creating an optimal index, start with = -- vendor_id in your case.

Then you get one crack at a "range" -- updated_at or (in the first case) code.

Also (or alternatively) "covering" the query can be beneficial. This is where all the columns mentioned in the SELECT exist in the INDEX. In the case of SELECT *, that is all columns in the table, and that is usually to many. When doing code, count(*), only 3 or 4 columns are mentioned, so "covering" may be practical and beneficial.

The PRIMARY KEY (id) is included implicitly in any secondary key.

It is handy to provide the entire EXPLAIN when asking questions like these. It is instructive to discuss "key_len" and how that tells you how many columns it used. EXPLAIN FORMAT=JSON SELECT ... is even better, since it is explicit.

Why did it pick (vendor_Id, code, feedback, updated_At, id) for the first query? It is missing created_at, so it is not "covering". Probably it decided that vendor_id (=) plus code (range) was slightly better than vendor_id, updated_At. I would expect the latter (aka "Index1") to be better because it might be able to consume the ORDER BY.

index1 (vendor_Id, updated_At) is redundant (and can be DROPped) because of codeIndex (vendor_Id, updated_At, code).

The second query needs (vendor_Id, updated_At, code) -- =, "range", "covering". The query can be performed in the index, without touching the data.

The third query adds feedback; so it needs the columns of (vendor_Id, code, feedback, updated_At), but it is hard to know whether that order is best or (vendor_Id, updated_At, code, feedback).

What version are you using? 5.6, 5.7, and 8.0 have each made significant changes to optimizations in this area.

BTW, and code is not null is redundant in the first query.

The first query cannot stop after 10 rows; this is because there is more than one "range" in the WHERE.

Answer 2

More of a comment than an answer, but it felt a bit long for a comment so I'll add it as an answer.

From my limited experience with MySQL the optimizer takes some weird decisions from time to time. Here is one observation I did:

What strategy does MySQL optimizer use when choosing between two indexes?

In short, it appeared as if the optimizer picked the first possible index even though there where better indexes declared later. You may try to create the indexes in a different order and see if that changes what index it chooses.

Answer 3

Thank you Rick for the explanation. I do agree the index1 (vendor_Id, updated_At) is redundant. We are currently using AWS Aurora which uses MYSQL 5.6. This is not an answer but due to length of my comment, I had to post it as a Answer.

I made the following new indexes :

codeIndex - ['vendor_Id', 'updated_At', 'code']
feedbackIndex - ['vendor_Id', 'updated_At', 'code', 'feedback']

Now, I ran the following 2 queries :

Query 1:

 EXPLAIN FORMAT=JSON select  'id', 'customerName', 'customerContactNumber', 'code', 'feedback', 'updated_At'
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
      and  code >=0
      and  code <= 6
    order by  updated_At DESC
    limit  10 
{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "Feedback",
        "access_type": "ref",
        "possible_keys": [
          "codeIndex",
          "feedbackIndex"
        ],
        "key": "codeIndex",
        "used_key_parts": [
          "vendor_Id"
        ],
        "key_length": "5",
        "ref": [
          "const"
        ],
        "rows": 1,
        "filtered": 100,
        "using_index": true,
        "attached_condition": "((`dEbill`.`Feedback`.`updated_At` < '2017-11-15 12:58:12.387') and (`dEbill`.`Feedback`.`updated_At` >= '2017-11-01 00:00:00.000') and (`dEbill`.`Feedback`.`code` >= 0) and (`dEbill`.`Feedback`.`code` <= 6))"
      }
    }
  }
}

DB selects codeIndex which seems like a good idea. Since, I added fields to Select should I include those fields in the index as covering ? I thought it will just make the index table too huge for amount of fields I am using.

Query 2 :

EXPLAIN FORMAT=JSON  select  code, count(*) as count
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
      and  feedback is not null
    group by  code;



{
  "query_block": {
    "select_id": 1,
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "table": {
        "table_name": "Feedback",
        "access_type": "ref",
        "possible_keys": [
          "codeIndex",
          "feedbackIndex"
        ],
        "key": "codeIndex",
        "used_key_parts": [
          "vendor_Id"
        ],
        "key_length": "5",
        "ref": [
          "const"
        ],
        "rows": 1,
        "filtered": 100,
        "index_condition": "((`dEbill`.`Feedback`.`updated_At` < '2017-11-15 12:58:12.387') and (`dEbill`.`Feedback`.`updated_At` >= '2017-11-01 00:00:00.000') and (`dEbill`.`Feedback`.`code` is not null))",
        "attached_condition": "((`dEbill`.`Feedback`.`vendor_Id` <=> 1) and (`dEbill`.`Feedback`.`feedback` is not null))"
      }
    }
  }
}

In this case the DB selects codeIndex --> I thought feedbackIndex was a better choice. Your thoughts ? The output remains the same even when I define feedbackIndex as ['vendor_Id', 'code', 'feedback', 'updated_At']. Also,at this moment all are tables are empty.