Create Index in mysql not working

Question

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]

Quassnoi · Accepted Answer · 2012-11-14 21:54:20Z

6

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.

answered Nov 14, 2012 at 21:54

Quassnoi

427k94 gold badges627 silver badges622 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Josh Correia · Accepted Answer · 2024-10-18 05:53:58Z

4

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.

edited Oct 18, 2024 at 5:53

Josh Correia

4,4003 gold badges42 silver badges67 bronze badges

answered Nov 14, 2012 at 21:46

Ray

41.6k22 gold badges110 silver badges142 bronze badges

14 Comments

Quassnoi Over a year ago

This won't preserve ASC / DESC but will create the index indeed.

Washu Over a year ago

and what about the INCLUDE? I mean i have tried to do it with it but i just can't

Quassnoi Over a year ago

@Washu: MySQL does not support non-key columns in indexes.

Michael Fredrickson Over a year ago

@Quassnoi From the MySql docs: These keywords (ASC and DESC) are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

Quassnoi Over a year ago

@Washu: it's not that MySQL is just a free SQL Server with a funny syntax. There are things it just does not implement.

|

Collectives™ on Stack Overflow

Create Index in mysql not working

2 Answers 2

Comments

14 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

14 Comments

Linked

Related