mysql> DROP TABLE IF EXISTS test.employees

mysql> CREATE TABLE test.employees (
    id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
) PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p2 VALUES LESS THAN (300),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

mysql> CREATE HYBRID INDEX h_idx ON test.employees(id, fname, store_id) PARAMETER '{
    "inverted": {
        "columns": ["id", "fname", "store_id"]
    },
    "sorting_key": {
        "columns": ["id", "fname", "store_id"],
        "order": ["asc", "desc", "asc"]
    },
    "sharding_key":{
        "columns":["id","fname","store_id"]
    }
}';

mysql> INSERT INTO test.employees (id, fname, lname, store_id, department_id) VALUES
    (1,         'Bob', 'Taylor',        1, 2), 
    (2,         'Frank', 'Williams',    101, 2),
    (3,         'Ellen', 'Johnson',     201, 4), 
    (4,         'Jim', 'Smith',         301, 4),
    (101,       'Mary', 'Jones',        2, 1), 
    (102,       'Linda', 'Black',       102, 3),
    (103,       'Ed', 'Jones',          202, 1), 
    (104,       'June', 'Wilson',       301, 1),
    (105,       'Andy', 'Smith',        3, 3), 
    (201,       'Lou', 'Waters',        103, 4),
    (202,       'Jill', 'Stone',        203, 4), 
    (203,       'Roger', 'White',       303, 2),
    (204,       'Howard', 'Andrews',    4, 2), 
    (301,       'Fred', 'Goldberg',     104, 3),
    (302,       'Barbara', 'Brown',     204, 3), 
    (303,       'Alice', 'Rogers',      304, 2),
    (304,       'Mark', 'Morgan',       5, 3), 
    (305,       'Karen', 'Cole',        105, 2);

wait> shard_progress table=test.employees index=h_idx;

mysql> SELECT count(*) FROM test.employees USE INDEX (h_idx) where id > 1;
+----------+
| count(*) |
+----------+
|       17 |
+----------+

mysql> SELECT count(*) FROM test.employees PARTITION (p1) USE INDEX (h_idx) where id > 101;
+----------+
| count(*) |
+----------+
|        4 |
+----------+

mysql> INSERT INTO test.employees (id, fname, lname, store_id, department_id) VALUES
    (116,       'Andy1', 'Smith1',      116, 3),
    (117,       'Andy2', 'Smith2',      117, 3),
    (118,       'Andy3', 'Smith3',      118, 3),
    (119,       'Andy4', 'Smith4',      119, 3),
    (120,       'Andy5', 'Smith5',      120, 3);

wait> shard_progress table=test.employees index=h_idx;

mysql> SELECT count(*) FROM test.employees USE INDEX (h_idx) where id > 1;
+----------+
| count(*) |
+----------+
|       22 |
+----------+

mysql> SELECT count(*) FROM test.employees PARTITION (p1) USE INDEX (h_idx) where id > 101;
+----------+
| count(*) |
+----------+
|        9 |
+----------+