Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Bug: Compound indexes not applied in Queries #2900

Open
2 tasks done
leefordjudes opened this issue Oct 27, 2023 · 2 comments · May be fixed by #3910
Open
2 tasks done

Bug: Compound indexes not applied in Queries #2900

leefordjudes opened this issue Oct 27, 2023 · 2 comments · May be fixed by #3910
Assignees
@emmanuel-keller
Labels
bug Something isn't working topic:indexing This is related to indexing and full-text search topic:surrealql This is related to the SurrealQL query language

Comments

@leefordjudes
Copy link

Describe the bug

Compound indexes not applied in Queries

Steps to reproduce

  1. prepare like the following:
DEFINE TABLE student SCHEMAFULL;

DEFINE FIELD name ON student TYPE string;
DEFINE FIELD subject ON student TYPE string;
DEFINE FIELD grade ON student TYPE string;

DEFINE INDEX compound_idx ON student FIELDS subject,grade;

INSERT INTO student [
    {
        name: "Tobie",
        subject: "English",
        grade: "A"
    },
    {
        name: "Muthu",
        subject: "Maths",
        grade: "B"
   },
];

INFO FOR TABLE student;

SELECT name FROM student WHERE subject = "English" AND grade = "A" EXPLAIN;
SELECT name FROM student WITH INDEX compound_idx WHERE subject = "English" OR grade = "A" EXPLAIN;

The result of last 4 queries:

// -------- Query 6 -------- (INSERT INTO)

[
    {
        "grade": "A",
        "id": "student:11bra2b04zr2fvmgdrml",
        "name": "Tobie",
        "subject": "English"
    },
    {
        "grade": "B",
        "id": "student:xtnv9725ejx233yz58cs",
        "name": "Muthu",
        "subject": "Maths"
    }
]

// -------- Query 7 -------- (INFO FOR TABLE)

{
    "events": {},
    "fields": {
        "grade": "DEFINE FIELD grade ON student TYPE string",
        "name": "DEFINE FIELD name ON student TYPE string",
        "subject": "DEFINE FIELD subject ON student TYPE string"
    },
    "indexes": {
        "compound_idx": "DEFINE INDEX compound_idx ON student FIELDS subject, grade"
    },
    "lives": {},
    "tables": {}
}

// -------- Query 8 -------- (Simple SELECT)

[
    {
        "detail": {
            "table": "student"
        },
        "operation": "Iterate Table"
    },
    {
        "detail": {
            "reason": "NO INDEX FOUND"
        },
        "operation": "Fallback"
    }
]

// -------- Query 9 -------- (SELECT + WITH INDEX)

[
    {
        "detail": {
            "table": "student"
        },
        "operation": "Iterate Table"
    },
    {
        "detail": {
            "reason": "NO INDEX FOUND"
        },
        "operation": "Fallback"
    }
]

Kindly please apply the index wherever it is needed.
I am doing migration work. due to not applying indexes, it took a long time.
if it goes like this, it takes many months to migrate our db.

Expected behaviour

Kindly please apply indexes wherever it is needed.

SurrealDB version

surreal 1.0.0

Contact Details

No response

Is there an existing issue for this?

  • I have searched the existing issues

Code of Conduct

  • I agree to follow this project's Code of Conduct
@leefordjudes leefordjudes added bug Something isn't working triage This issue is new labels Oct 27, 2023
@kearfy kearfy added topic:surrealql This is related to the SurrealQL query language topic:indexing This is related to indexing and full-text search labels Jan 16, 2024
@prabirshrestha
Copy link

Hit the same issue. I have a file table where I index the storage name and parent path and it doesn't seem to use index.

SELECT name, size, mtime, parent, storage FROM file WHERE storage='Downloads' AND parent='/'

@prabirshrestha prabirshrestha mentioned this issue Feb 9, 2024
Closed
2 tasks
@emmanuel-keller emmanuel-keller removed the triage This issue is new label Feb 9, 2024
@emmanuel-keller
Copy link
Contributor

emmanuel-keller commented Apr 18, 2024 •
edited

Would you consider creating two indexes?

Eventually the compound index will work on the AND query (we are currently working on it)

SELECT name FROM student WHERE subject = "English" AND grade = "A" EXPLAIN;

For the OR query you will have to create two indexes:

DEFINE INDEX subject_idx ON student FIELDS subject;
DEFINE INDEX grade_idx ON student FIELDS grade;
SELECT name FROM student WHERE subject = "English" OR grade = "A" EXPLAIN;
[
    {
        "detail": {
            "plan": {
                "index": "subject_idx",
                "operator": "=",
                "value": "English"
            },
            "table": "student"
        },
        "operation": "Iterate Index"
    },
    {
        "detail": {
            "plan": {
                "index": "grade_idx",
                "operator": "=",
                "value": "A"
            },
            "table": "student"
        },
        "operation": "Iterate Index"
    },
    {
        "detail": {
            "type": "Store"
        },
        "operation": "Collector"
    }
]

@emmanuel-keller emmanuel-keller linked a pull request Apr 18, 2024 that will close this issue
Draft
2 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@emmanuel-keller emmanuel-keller

Labels
bug Something isn't working topic:indexing This is related to indexing and full-text search topic:surrealql This is related to the SurrealQL query language
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Query planner detects compound indexes surrealdb/surrealdb
4 participants
@prabirshrestha @emmanuel-keller @leefordjudes @kearfy