2

My understanding is that when designing a composite index a column, which will be used for range tests should be placed at the end of this index, because the usage of the columns in composite indexes stops after the first range column.

The things is that EXPLAIN shows that also a column after a range column in used by optimizer. That is why I am confused and not sure how to properly design my composite indexes.

Below is a simplified example of my table. For each date DateP there is a number of objects Object. Each Object has 3 underlying sub-objects subObject. subObject is a unique name for each DateP and it is derived from Object by adding a number. subObject has an integer identifier subObjectId, which is usually 0, 1 or 2 and it is unique within the same Object.

subObject Object DateP subObjectId counter1
aaa1 aaa 2019-06-01 0 10
aaa2 aaa 2019-06-01 1 13
aaa3 aaa 2019-06-01 2 11
bbb1 bbb 2019-06-01 0 9
bbb2 bbb 2019-06-01 1 6
bbb3 bbb 2019-06-01 2 7
aaa1 aaa 2019-06-02 0 14
aaa2 aaa 2019-06-02 1 12
aaa3 aaa 2019-06-02 2 16

CREATE TABLE code is below. Table engine is MyISAM and server is MySQL 8.

CREATE TABLE `testTab` (
  `DateP` date NOT NULL,
  `Object` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `subObject` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `subObjectId` int NOT NULL,
  `counter1` int DEFAULT NULL,
  ......
  PRIMARY KEY (`subObject`,`DateP`,`subObjectId`) USING BTREE,
  KEY `Object` (`Object`,`DateP`,`subObjectId`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC 

The most unique column from the KEY is Object, followed by DateP. The least unique is subObjectId. As far as I know the uniqueness of the columns also matters for their order in a composite index - the most unique should be leftmost.

Following you can see different versions of EXPLAIN depending on the conditions in WHERE clause.

  1. All three columns from the composite index are presented. In the middle is the range test of DateP. Nevertheless subObjectId column in also used by Optimizer after the range test. key_len is 31.
EXPLAIN SELECT DateP,Object,SUM(counter1)
FROM testTab
WHERE Object='aaa' AND DateP>='2019-06-01' AND DateP<='2019-06-10' AND (subObjectId=0 OR subObjectId=1)
GROUP BY Object,DateP
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | testTab | NULL       | range | Object        | Object | 31      | NULL |   28 |    19.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
  1. The first and second columns are presented in WHERE clause, so it finishes with the range test. key_len is 27.
EXPLAIN SELECT DateP,Object,SUM(counter1)
FROM testTab
WHERE Object='aaa' AND DateP>='2019-06-01' AND DateP<='2019-06-10'
GROUP BY Object,DateP
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | testTab | NULL       | range | Object        | Object | 27      | NULL |   29 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
  1. Only the first column is used in WHERE. key_len is 24.
EXPLAIN SELECT DateP,Object,SUM(counter1)
FROM testTab
WHERE Object='aaa' 
GROUP BY Object,DateP
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | testTab | NULL       | ref  | Object        | Object | 24      | const |  417 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+

My conclusion is that all three columns from the composite index can be used despite that in the middle is a column for range tests. But can I expect that the optimizer will always respect the third column?

In my current design KEY is designed with DateP column at the end.

KEY `Object` (`Object`,`subObjectId`,`DateP`) USING BTREE

But this order is not suitable for GROUP BY clause, which is:

GROUP BY Object,DateP

In this case for grouping I guess only Object column is used, because I skip the middle column DateP. Query execution ends with Using temporary.

+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | testTab | NULL       | range | Object        | Object | 31      | NULL |   20 |   100.00 | Using index condition; Using temporary |
+----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------------+
3
  • "Using index condition" means the next: the server uses the index not like sorted columns set but only like compact version of the table. I.e. it uses index scanning, not index seeking. Depends on the columns order the server may use an index prefix for pre-seeking (and grouping) with subsequent scanning, or it can use complete index scanning (and temprowset for grouping). Looking at the query I'd recommend you to include counter1 column into the index expression, this will the index to be covering one, and hence this will improve.
    – Akina
    Mar 11 at 12:41
  • @Akina unfortunately I cannot include counter1, because it is just one column out of many. For simplicity in the example I dropped the rest columns.
    – Ivaylo
    Mar 11 at 14:08
  • 1
    I see... I mention the improvement provided by the covering index in theory. See dbfiddle.uk/c-g50AXl
    – Akina
    Mar 11 at 15:11

1 Answer 1

1

Random notes:

  • "composite index used for range tests should [have the range column] at the end of this index -- true; that's 'should', not 'must'
  • "the most unique [column] should be leftmost [in an index]" -- myth
  • CHAR should be used only for fixed length strings.
  • MyISAM should be abandoned for InnoDB.
  • "But can I expect that the optimizer will always respect the third column?" -- Try WHERE Object='aaa' AND subObjectId=1 (without testing/grouping DateP).

Let's discuss further my first bullet item.

  • For an index to be efficiently used, the WHERE needs to walk through it, using each item it finds. Skipping over rows makes it less efficient.
  • When all columns that show up anywhere in the SELECT are found anywhere in one INDEX, that index is called "covering". (None of your three examples show that -- because of needing counter1.) A covering index has the extra advantage of not needing to bounce between the index and te data repeated to find more columns.
  • So, the optimal index is both covering and ordered as follows:
  1. all columns tested via just = (or IS NULL) first.
  2. the best non-= test next.
  3. other columns to make it "covering". (This runs into problems if you need lots of columns or big columns.)

More notes:

  • GROUP BY and/or ORDER BY may tug at the ordering -- to avoid a sort. Note that all 3 of your examples can avoid sorting because the index starts with Object, DateP in the desired order.
  • A single INDEX(Object, DateP, subObjectid, counter) would be optimal for the first query and semi-optimal for the other two.

More: Index Cookbook

4
  • As usual you provide very useful advices :) I altered the index of my big table, so that to place the range column DateP in the middle. All the tests that I performed showed that no matter that the range scan column is in second position in the composite index, also the third member subObjectid is used by the optimizer. I don't know how it come that on many places I read that the usage of composite index is stopped after the first range column. Apparently this is not true.
    – Ivaylo
    Mar 13 at 14:00
  • EXPLAIN does not give the complete story, especially on whether or not the columns after a "range" are useful. "Covering" is indicated by "Using index"; "Using index condition" is less useful (and may not be useful for MyISAM).
    – Rick James
    Mar 13 at 17:23
  • After changing the index from INDEX(Object, subObjectid, DateP ) to INDEX(Object, DateP, subObjectid) I removed some of the actions in Extra column of EXPLAIN. It used to be Using index condition; Using temporary; Using filesort, now it is Using index condition. Apparently GROUP BY AND ORDER BY were optimized. Also key_len shows that all columns in index are used - I hope EXPLAIN is not misleading me as you suggest that could happen. I can't create covering index, because I have quite many columns in table.
    – Ivaylo
    Mar 14 at 8:52
  • Using index implies "covering".
    – Rick James
    Mar 15 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.