mysqlsqlindexing

Create Index in mysql not working


Im using MYSQL Server 5.0 and im trying to use the next sentence of SQL Server and is not working at all. Please is there any way to apply this to Mysql also. Thank you very much.

CREATE NONCLUSTERED INDEX [IX_Dave_General] ON [dbo].[Stock]

    (
            [ArticleID] ASC,
              [TranDate] DESC,
            [TranCode] ASC
    )
    INCLUDE ( [Items],
    [Price]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Solution

  • The original SQL Server index used two features: covering indexes (with non-key columns under INCLUDE) and mixed sort direction (ASC and DESC columns).

    Neither of those are supported by MySQL.

    If you still need the index (say for exact matches over all three columns), use @Ray's answer.