fix: handle count multiple PK & edge cases more gracefully #6870
Conversation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
currently we use concatenation of multiple primary keys and a COUNT DISTINCT of that to figure out how many records we have matched in a query. however, that fails if the records have keys when the keys are ambigious when concatenated (`"A", "AA"` & `"AA", "A"`) the fact that we do a distinct can also be a performance impact that isn't needed when we aren't doing joins as such, in MySQL & Postgres we can use the built in counting of multiple distinct values to resolve some of the issues, and in other environments we can make it SLIGHTLY better by adding delimiters between the concatenated values. It is not perfect because it technically could run into the same issue if the delimiters are in the primary keys but it's BETTER in most cases. also, in cases where we do not perform any joins we can short circuit all of this and do a much more performant `COUNT(1)` operation fixes typeorm#5989 fixes typeorm#5314 fixes typeorm#4550
imnotjames
force-pushed
the
fix/count-issues
branch
from
225a419
to
5a4c61a
Compare
This was referenced Oct 8, 2020
17 tasks
zaro
pushed a commit
to zaro/typeorm
that referenced
this pull request
Jan 12, 2021
) currently we use concatenation of multiple primary keys and a COUNT DISTINCT of that to figure out how many records we have matched in a query. however, that fails if the records have keys when the keys are ambigious when concatenated (`"A", "AA"` & `"AA", "A"`) the fact that we do a distinct can also be a performance impact that isn't needed when we aren't doing joins as such, in MySQL & Postgres we can use the built in counting of multiple distinct values to resolve some of the issues, and in other environments we can make it SLIGHTLY better by adding delimiters between the concatenated values. It is not perfect because it technically could run into the same issue if the delimiters are in the primary keys but it's BETTER in most cases. also, in cases where we do not perform any joins we can short circuit all of this and do a much more performant `COUNT(1)` operation fixes typeorm#5989 fixes typeorm#5314 fixes typeorm#4550
|
Hello, |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
currently we use concatenation of multiple primary keys and a
COUNT DISTINCT of that to figure out how many records we have
matched in a query.
however, that fails if the records have keys when the keys
are ambigious when concatenated (
"A", "AA"&"AA", "A")the fact that we do a distinct can also be a performance impact
that isn't needed when we aren't doing joins
as such, in MySQL & Postgres we can use the built in counting
of multiple distinct values to resolve some of the issues,
and in other environments we can make it SLIGHTLY better by
adding delimiters between the concatenated values. It is not
perfect because it technically could run into the same issue
if the delimiters are in the primary keys but it's BETTER
in most cases.
also, in cases where we do not perform any joins we can
short circuit all of this and do a much more performant
COUNT(1)operationfixes #5989
fixes #5314
fixes #4550
fixes: #4998