How do I use an index with Filter?

[lateefj]

I have been trying to figure out how to use an index with the Filter:

rows, err := r.Table(CensusTableName).GetAllByIndex("Name").Filter(map[string]interface{}{"Name": name}).OrderBy(rethink.Desc("Year")).Run(session)

I made sure the index has been created but still there is no performance advantage using an index. Do I have the wrong syntax?

[dancannon]

You might not be getting a performance increase due to the fact that you are filtering multiple times. Try the following code:

rows, err := r.Table(CensusTableName).GetAllByIndex("Name", name).OrderBy(rethink.Desc("Year")).Run(session)

[lateefj]

Sorry about that! That was exactly what I was looking for. Will this also work for multiple column index?

[dancannon]

It should work with compound indexes. For example:

rows, err := r.Table(CensusTableName).GetAllByIndex("Name", field1, field2).OrderBy(rethink.Desc("Year")).Run(session)

Where the index Name contains two fields.

[lateefj]

Thanks so helpful I will try that next.

[wildattire]

@lateefj Did you ever get compound indexes working with gorethink? It works in the rethink data explorer but does not appear to work with gorethink at all. That is to say, it returns 0 rows for the exact same query that the data explorer returns rows.

[wildattire]

@dancannon Does the test suite cover compound indexes? Have other people successfully used them? They really don't seem to work at all. Regular secondary indexes work, and compound indexes work with the javascript driver, but not with gorethink.

[lateefj]

@wildattire I couldn't get them to work.

[robert-zaremba]

@wildattire they works for me, but let's move a discussion to #65.

[skatiyar]

@dancannon couldn't get following to work.

rows, err := r.Table(CensusTableName).GetAllByIndex("Name", value).OrderBy(rethink.Desc("Year")).Run(session)

Both Name and Year are indexed. Should they be compound indexed??

[dancannon]

I just create this simple program which seems to show it working. Could you maybe explain what is going wrong with your program?

package main

import (
    r "github.com/dancannon/gorethink"
    "github.com/davecgh/go-spew/spew"
)

func main() {
    r.SetVerbose(true)
    s, err := r.Connect(r.ConnectOpts{
        Address: "localhost:28015",
    })
    if err != nil {
        panic(err)
    }

    r.DB("issues").TableDrop("census").Exec(s)
    r.DB("issues").TableCreate("census").Exec(s)

    r.DB("issues").Table("census").IndexCreate("Name").Exec(s)
    r.DB("issues").Table("census").IndexWait().Exec(s)

    if err := r.DB("issues").Table("census").Insert(map[string]interface{}{
        "Name": "John Smith",
        "Year": 1970,
    }).Exec(s); err != nil {
        panic(err)
    }

    cursor, err := r.DB("issues").Table("census").GetAllByIndex("Name", "John Smith").OrderBy(r.Desc("Year")).Run(s)
    if err != nil {
        panic(err)
    }

    var v interface{}
    err = cursor.One(&v)
    if err != nil {
        panic(err)
    }

    spew.Dump(v)
}

[skatiyar]

@dancannon Thanks for quick reply. It turns out that above doesn't work if orderBy is on indexed field.

r.DB("issues").Table("census").GetAllByIndex("Name", "John Smith").OrderBy(r.Desc("Year")).Run(s)

If Year is indexed and you give {Index: r.Desc("Year")} it gives error.

In that case orderBy should be

r.DB("issues").Table("census").OrderBy(r.OrderByOpts{Index: r.Desc("Year")}).Filter(map[string]interface{}{"Name": "John Smith"}).Run(s)