How to make a binding when the primary key is not an ID?

Question

MySQL

There is a table with goods that have clearly indicated analogues.
Binding of analogues was planned to be done by id, which is auto_increment.
There are about 2 million records in the table today.
In the table, the goods are presented in discord, without any sorting. What causes the overflow of the temporary folder, because the request with pagination constantly sorts the selection alphabetically, and generally speaking, the selection is not always easy to take a pack and display - sometimes you need to display it from a certain category, for example. Moreover, at the top, display those that are in a particular category, and behind them - those that are in child categories.
It is planned to issue a list to the client sorted by the `name` field, if I understand correctly, so that each request does not have to re-sort the entire table - you need to put the primary key on `name` for good.
But how then to bind similar products, understanding that name is a long string, and if I switch the primary key, then auto_increment can no longer be done?
Or is there some way to get out?
Thank you.

Answer 1

if I understand correctly, then so that each query does not have to re-sort the entire table

I'm afraid you misunderstand. In general, a primary key does not guarantee any magical automatic sorting. In some engines, such sorting can indeed be, but you did not indicate what exactly you use.
In some engines, you can auto-increment not only by the primary key.
Read about indexes, including composite ones. The first link from Google for "mysql compound indexes" https://ruhighload.com/post/%D0%A0%D0%B0%D0%B1%D0%...

Answer 2

You can add an ID field with auto_increment to an existing table and it will be automatically generated for all products.

-> ADD id INT NOT NULL AUTO_INCREMENT,
-> ADD PRIMARY KEY (id);

Full instructions here .

Answer 3

if I understand correctly, so that each query does not have to re-sort the entire table - you need to put the primary key on `name` for good

You misunderstand. It is enough to make an index on the `name` field. The table is not re-sorted, just records are selected by index.