Select by date range and projections for best performance-

AlexAlex Registered User

Hi,
I'm doing typical join of two long tables by composite primary keys and filtering a range by non-key date field.

CREATE TABLE operations (pk1 int, pk2 int, date date, somedata int, PRIMARY KEY(pk1, pk2))
CREATE TABLE additional (pk1 int, pk2 int, somedata int, PRIMARY KEY(pk1, pk2))

SELECT
op.pk1
, op.pk2
, op.date
, op.somedata
, ad.somedata
FROM operations AS op
JOIN additional AS ad
ON op.pk1 = ad.pk1
AND op.pk2 = ad.pk2
WHERE op.date BETWEEN '...' AND '...'

There are two points to optimize:

  • join by primary key and
  • filtering by date.

What projections I should create to achieve the best performance in this case?

Answers

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file