Way to generate Multicolumn Indexes? #762

New Issue

lunny · 2017-10-29T05:13:16Z

TangMonk commented

2017-10-29 05:13:16 +00:00

(Migrated from github.com)

https://www.postgresql.org/docs/9.6/static/indexes-multicolumn.html
https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

seems there is only create single column index.

https://www.postgresql.org/docs/9.6/static/indexes-multicolumn.html https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html seems there is only create single column index.

lunny commented

2017-10-29 05:33:22 +00:00

type MyTable struct {
    Id int64
    Column1 string `xorm:"index(s)"`
    Column2 string `xorm:"index(s)"`
}

```Go type MyTable struct { Id int64 Column1 string `xorm:"index(s)"` Column2 string `xorm:"index(s)"` } ```

TangMonk commented

2017-10-29 10:29:37 +00:00

(Migrated from github.com)

@lunny this just create separate index for Column1 and Column2, will generate sql:

CREATE INDEX Column1_idx ON MyTable (Column1);
CREATE INDEX Column2_idx ON MyTable (Column2);

How to generate this following sql?

CREATE INDEX Columns_idx ON MyTable (Column1, Column2);

or even more condition:

CREATE INDEX Columns_idx ON MyTable (Column1, Column2, CreatedAt desc);

@lunny this just create separate index for `Column1` and `Column2`, will generate sql: ``` CREATE INDEX Column1_idx ON MyTable (Column1); CREATE INDEX Column2_idx ON MyTable (Column2); ``` How to generate this following sql? ``` CREATE INDEX Columns_idx ON MyTable (Column1, Column2); ``` or even more condition: ``` CREATE INDEX Columns_idx ON MyTable (Column1, Column2, CreatedAt desc); ```

lunny commented

2017-10-30 02:50:43 +00:00

Have you tried this code?

type MyTable struct {
    Id int64
    Column1 string `xorm:"index(s)"`
    Column2 string `xorm:"index(s)"`
}
engine.CreateIndexes(new(MyTable))

Have you tried this code? ```Go type MyTable struct { Id int64 Column1 string `xorm:"index(s)"` Column2 string `xorm:"index(s)"` } engine.CreateIndexes(new(MyTable)) ```

TangMonk commented

2017-10-30 03:17:34 +00:00

(Migrated from github.com)

@lunny , Sorry, it created the index, I am not try it before.

But how to create index with specifies descending sort order?

CREATE INDEX Columns_idx ON MyTable (Column1, Column2, CreatedAt desc);

@lunny , Sorry, it created the index, I am not try it before. But how to create index with specifies descending sort order? ``` CREATE INDEX Columns_idx ON MyTable (Column1, Column2, CreatedAt desc); ```

lunny commented

2017-10-30 03:29:21 +00:00

With the same name on the quote after index will be a group indexes. Currently it didn't support custom index sequence.

type MyTable struct {
    Id int64
    Column1 string `xorm:"index(s)"`
    Column2 string `xorm:"index(s)"`
    CreatedAt time.Time `xorm:"created index(s)"`
}

With the same name on the quote after index will be a group indexes. Currently it didn't support custom index sequence. ```Go type MyTable struct { Id int64 Column1 string `xorm:"index(s)"` Column2 string `xorm:"index(s)"` CreatedAt time.Time `xorm:"created index(s)"` } ```

TangMonk commented

2017-10-30 03:31:28 +00:00

(Migrated from github.com)

thanks, please notice CreatedAt desc on above sql, the descending sort seems cannot created

thanks, please notice `CreatedAt desc` on above sql, the descending sort seems cannot created

lunny commented

2017-10-30 03:32:45 +00:00

It's not supported.

lauyoume commented

2018-05-31 08:12:56 +00:00

(Migrated from github.com)

@lunny Can you add support for custom index sequence?

lunny commented

2018-05-31 08:17:07 +00:00

@lauyoume any suggestion about the xorm tag format to support custom index sequence? How about

type MyTable struct {
    Id int64
    Column1 string `xorm:"index(s desc)"`
    Column2 string `xorm:"index(s)"`
    CreatedAt time.Time `xorm:"created index(s)"`
}

@lauyoume any suggestion about the xorm tag format to support custom index sequence? How about ```Go type MyTable struct { Id int64 Column1 string `xorm:"index(s desc)"` Column2 string `xorm:"index(s)"` CreatedAt time.Time `xorm:"created index(s)"` } ```

lauyoume commented

2018-05-31 18:07:01 +00:00

(Migrated from github.com)

@lunny
Not only index sequence, Now using tag can't fully describe the order of the columns in the index.
Your implementation is currently based on the order of the structure fields.

If I want to create two indexs like this, how to describe the order of col1/col2/col3.

alter table MyTable  add index idx_col1_col2(col1, col2);
alter table MyTable  add index idx_col3_col2_col1(col3, col2, col1);

beego orm, Using interface to custom index

// for index
func (u * MyTable) TableIndex() [][]string {
    return [][]string{
        []string{"col1", "col2"},
        []string{"col3", "col2", "col1"},
        []string{"col4", "col3 desc"},
    }
}
// for unique
func (u * MyTable) TableUnique() [][]string {
    return [][]string{
        []string{"col4", "col5"},
    }
}

@lunny Not only index sequence, Now using tag can't fully describe the order of the columns in the index. Your implementation is currently based on the order of the structure fields. If I want to create two indexs like this, how to describe the order of col1/col2/col3. ```sql alter table MyTable add index idx_col1_col2(col1, col2); alter table MyTable add index idx_col3_col2_col1(col3, col2, col1); ``` beego orm, Using interface to custom index ```golang // for index func (u * MyTable) TableIndex() [][]string { return [][]string{ []string{"col1", "col2"}, []string{"col3", "col2", "col1"}, []string{"col4", "col3 desc"}, } } // for unique func (u * MyTable) TableUnique() [][]string { return [][]string{ []string{"col4", "col5"}, } } ```

lunny commented

2018-06-01 01:13:01 +00:00

How about

type MyTable struct {
    Id int64
    Column1 string `xorm:"index(s desc, 2)"`
    Column2 string `xorm:"index(s,3)"`
    CreatedAt time.Time `xorm:"created index(s,1)"`
}

How about ```Go type MyTable struct { Id int64 Column1 string `xorm:"index(s desc, 2)"` Column2 string `xorm:"index(s,3)"` CreatedAt time.Time `xorm:"created index(s,1)"` } ```

lauyoume commented

2018-06-01 08:08:27 +00:00

(Migrated from github.com)

It's a good solution, may you add support for this?

lunny commented

2018-06-03 01:36:51 +00:00

@lauyoume I will try this.

giter commented

2018-10-10 15:10:52 +00:00

(Migrated from github.com)

@lunny any plan for this feature?

lunny commented

2018-10-11 13:46:56 +00:00

No plan currently. Maybe someone could send a PR to fix.

Sign in to join this conversation.

No Milestone

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#762