When I create an index in a simple way, everything works well:
create index qty_ix on vacancy_desc_to_words (qty);
But when I try to create NONCLUSTERED INDEX, I have problems: neither a Workbench not MySQL console does know the NONCLUSTERED and INCLUDE commands.
MySQL version in Linux:
mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper
How to use NONCLUSTERED/INCLUDE commands?
You don't need to specify that an index is NONCLUSTERED in MySQL, it's implicit from the table design.
Only the PRIMARY KEY or the first non-NULL UNIQUE KEY can be the clustered index, and they will be the clustered index without you choosing.
All the other indexes in the table are implicitly non-clustered.
I'm assuming the table uses the InnoDB storage engine, which is the default storage engine. If you use another storage engine, the rules may be different. For example, MyISAM doesn't support clustered indexes at all.
MySQL also doesn't support the concept of INCLUDE as Microsoft does. Only the columns named for the index are included.
You should keep in mind that each brand of SQL database has its own features and syntax. Indexes in particular are not mentioned in the SQL standard, so there's no obligation between vendors of SQL databases to adhere to any consistent set of features.
You should therefore familiarize yourself with the syntax and features of the brand of database that you use, and don't try to use features of one brand on another, without checking the documentation.
i found this , it may explain the thing, here but bottom line, the above syntax is not vaild in MYSQL , properly it is a SQLServer
In addition to what Bill says, INCLUDE can be simulated for non-UNIQUE indexes by simply tacking the extra columns onto the column list:
CREATE INDEX qty_ix ON vacancy_desc_to_words (qty, words_id);
