CODEWITHSUNDEEP
mysqlsqlindexing
Washu
Washu

Reputation: 835

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]

Upvotes: 2

Views: 4349

Answers (2)

Ray
Ray

Reputation: 41508

Assuming your table is Stock and you want a composite index with (ArticleID,TranDate, TranCode).

CREATE INDEX your_index_name ON Stock (ArticleID,TranDate, TranCode);

I have no idea if any of the other info is possible in MySQL for myISAM or InnoDb storage engines.

To include other fields to make a covering index just add them to the declaration:

CREATE INDEX your_index_name ON Stock (ArticleID,TranDate, TranCode, Items, Price);

I'm assuming the Items and Prices being 'included' in your statement are part of the Stock table.

Note the big sacrifice here is on insert and disk space to have such a large covering index.

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425833

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.

Upvotes: 6

Related Questions