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]

score 6 · Accepted Answer · answered Nov 14 '12 at 21:54

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.

Ray · Answer 2 · 2012-11-14T21:56:30.090

4

Assuming you're 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 id 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 Nov 14 '12 at 21:56

answered Nov 14 '12 at 21:46

Ray

40,256
21
101
138

1

This won't preserve `ASC / DESC` but will create the index indeed. – Quassnoi Nov 14 '12 at 21:47
and what about the INCLUDE? I mean i have tried to do it with it but i just can't – Washu Nov 14 '12 at 21:47
@Quassnoi That is correct. I don't think you have that degree of control over indexes in mysql – Ray Nov 14 '12 at 21:48
2

@Washu: `MySQL` does not support non-key columns in indexes. – Quassnoi Nov 14 '12 at 21:48
You can use ASC/DESC on Mysql but the INCLUDE i think is not available – Washu Nov 14 '12 at 21:48
1

@Quassnoi From the [MySql docs](http://dev.mysql.com/doc/refman/5.0/en/create-index.html): 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. – Michael Fredrickson Nov 14 '12 at 21:49
@Washu I don't know if ASC/DESC is available on Index creation... only order by select staments... – Ray Nov 14 '12 at 21:49
2

@Washu: it's not that `MySQL` is just a free `SQL Server` with a funny syntax. There are things it just does not implement. – Quassnoi Nov 14 '12 at 21:51
@Quassnoi Maybe I misunderstood... I thought you were saying that this answer was deficient because it didn't include the ASC and DESC keywords, and my comment was stating that these keywords are ignored by MySql for index creation. – Michael Fredrickson Nov 14 '12 at 21:52
@MichaelFredrickson: this is exactly what I meant by "won't preserve". – Quassnoi Nov 14 '12 at 21:55
@Washu If those values are in the table, you can add them to the index. see my edits – Ray Nov 14 '12 at 21:56
Thanks Ray yeah I'm going to try that right now, What I'm making is a FIFO inventory for my company and i saw this code though it would help but if only works for SQL I will have to change it. Later in the night I will post the results. Thanks everyone for the help – Washu Nov 14 '12 at 22:05
So what happens if Price is NULL? The only reason to use INCLUDE is the column is nullable. – Joshua Jun 04 '19 at 20:46
Assuming `ArticleID,TranDate, TranCode` point to a unique record, there will be no issue with a Price of null or items of null. – Ray Jun 05 '19 at 19:48

Create Index in mysql not working

2 Answers2

Linked