CODEWITHSUNDEEP
mysqlmysql-5.7
Taichi
Taichi

Reputation: 2597

Is it possible to specify sorting order to MySQL index?

I have a items table with category_id field. There is a specific rule to order the items by category_id. I usually sort the data like this:

SELECT * FROM items ORDER BY FIELD(category_id, 2, 5, 1, 4, 3)
-- In this example, the "rule" is sorting in order of (2, 5, 1, 4, 3)

In this case, simply creating an index on category_id field does not work to speed up sorting items, because the index sorts the category_id just ascending like (1, 2, 3, 4, 5).

Is it possible to specify the sorting rule when I CREATE INDEX on category_id field? (And then simply SELECT * FROM items ORDER BY category_id works)

Or do I have to create another field like sorted_category_id which is sorted according to the order rule?

Upvotes: 0

Views: 1222

Answers (2)

trincot
trincot

Reputation: 350770

Adding the column to the items table, with an index on it, would indeed be a solution focused on speed. By making it a generated column, you ensure consistency, and by making it a virtual column, you can move the extra data into an index (if you create it). So proceed like this:

ALTER TABLE items ADD (
  category_ord int GENERATED ALWAYS AS (FIELD(category_id, 2, 5, 1, 4, 3)) VIRTUAL 
);
CREATE INDEX idx_items_category_ord ON items(category_ord);

SELECT * FROM items ORDER BY category_ord;

Alternative

Alternatively, the normalised way is to add a column to the category table. This will have a slight performance impact if you have many categories, but does not pose that consistency problem, and saves space. To implement that idea, proceed as follows:

If you don't have that category table, then create it:

CREATE TABLE category(
  id int NOT NULL PRIMARY KEY,
  ord int NOT NULL,
  name varchar(100)
);

Populate the ord field (or whatever you want to call it) as desired:

INSERT INTO category(id, ord, name) VALUES
(1, 30, 'cat1'),
(2, 10, 'cat2'),
(3, 50, 'cat3'),
(4, 40, 'cat4'),
(5, 20, 'cat5');

And add an index on the ord column:

CREATE INDEX category_ord ON category(ord);

Now the query would be:

SELECT * 
FROM items 
INNER JOIN category 
ON items.category_id = category.id
ORDER BY category.ord;

The database engine can now decide to use the index on the ord column (or not), depending on its own analysis. If you want to force the use of it, you can use FORCE INDEX:

SELECT * 
FROM items 
INNER JOIN category FORCE INDEX category(category_ord) 
ON items.category_id = category.id
ORDER BY category.ord;

Note that the engine can use your index on the items.category_id as well, for value by value lookup.

Upvotes: 1

Taichi
Taichi

Reputation: 2597

Like Akina says, I can use Generated Columns. https://dev.mysql.com/doc/refman/8.0/en/create-index.html

Upvotes: 0

Related Questions