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.

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

QE: Optimize the compound id select #570

Closed
pimeys opened this issue Mar 10, 2020 · 2 comments
Closed

QE: Optimize the compound id select #570

pimeys opened this issue Mar 10, 2020 · 2 comments
Assignees
Labels
kind/improvement An improvement to existing feature and code. tech/engines/query engine Issue in the Query Engine
Milestone

Comments

@pimeys
Copy link
Contributor

pimeys commented Mar 10, 2020

In QE when using compound ids, the related fields select does something following:

WHERE ("id1" = 1 AND "id2" = 2) OR ("id1" = 3 AND "id2" = 4)

to search related records for given parent projections. This is very inefficient and will skyrocket the response times when having a larger set of projections. For now we've been optimizing the special case with just a single key with an IN statement that is orders of magnitude faster:

WHERE "id1" IN (1, 3)

We can do this with compound keys too, taking into account a slight difference how SQLite handles them, so for MySQL and PostgreSQL this will work:

WHERE ("id1", "id2") IN ((1, 2), (3, 4))

But for SQLite we need

WHERE ("id1", "id2") IN (VALUES (1, 2), (3, 4))

Implement optimizations for the compound ids and measure the effect with Chihiro.

@pimeys pimeys added kind/improvement An improvement to existing feature and code. tech/engines/query engine Issue in the Query Engine labels Mar 10, 2020
@pimeys pimeys added this to the Preview 24 New milestone Mar 10, 2020
@pimeys pimeys self-assigned this Mar 10, 2020
@pimeys
Copy link
Contributor Author

pimeys commented Mar 10, 2020

QE: #569
Quaint: prisma/quaint#92

@pimeys
Copy link
Contributor Author

pimeys commented Mar 16, 2020

Done and merged.

@janpio janpio closed this as completed Mar 16, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
kind/improvement An improvement to existing feature and code. tech/engines/query engine Issue in the Query Engine
Projects
None yet
Development

No branches or pull requests

3 participants