Mysql Question posted in
The official MySQL documentation can be found here.

MySQL specify primary key sorting order in secondary index

In MySQL 5.7.25 and MariaDB 10.6.15, using InnoDB by default to create below table

create table t1(
id serial,
employee_id int not null,
name varchar(20),
status tinyint,
created_at datetime(3) not null,
updated_at datetime(3) not null,
event CHAR not null,
primary key (id)
);

I know the pk is auto included in secondary index.
For this table, let’s say I want to create a secondary index on employee_id but for same employee_id order by id desc.

CREATE INDEX idx_employee_id_id_desc ON t1 (employee_id, id DESC);

Seems the order on id does not work, the show index command indicates idx_employee_id_id_desc.id is still A.
Does any one knows is this allowed in MySQL or MariaDB?
Thanks!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I tried, seems this works on MySQL 8+, but not 5.7. Not work on MariaDB 10.6.15 as well.

    In MySQL 8.0.37, when create index CREATE INDEX idx_employee_id_id_desc ON t1 (employee_id, id desc);

    Then show index from t1 will show

    t1  1   idx_employee_id_id_desc 1   employee_id  A
t1  1   idx_employee_id_id_desc 2   id           D

  2. 0

    As per mysql manual on creating indexes:

    A key_part specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order. The default is ascending if no order specifier is given. ASC and DESC are not permitted for HASH indexes. ASC and DESC are also not supported for multi-valued indexes. As of MySQL 8.0.12, ASC and DESC are not permitted for SPATIAL indexes.

    Example:

    CREATE INDEX idx1 ON t1 (col1) DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search