CODEWITHSUNDEEP
sqldatabaseclickhouse
Guru Vishnu Vardhan Reddy
Guru Vishnu Vardhan Reddy

Reputation: 349

Query optimization by reusing the inner query only once

Consider the below Query

SELECT
  FlightDate,
  FlightNum,
  Origin,
  Dest,
  AirlineID
FROM
  ontime1
WHERE 
  FlightDate >= (SELECT MIN(FlightDate) FROM ontime2 WHERE Origin='JFK') AND
  FlightDate <= (SELECT MAX(FlightDate) FROM ontime2 WHERE Origin='JFK') AND
  AirlineID IN (SELECT AirlineID FROM ontime2 WHERE Origin='JFK')
LIMIT 10;

ontime1 -> indexed based on FlightDate
ontime2 -> indexed based on Origin, Dest

Also joining the table is not optimal as the table data is so big

How the inner query can be reused without repeating the same query again and again?

Upvotes: 1

Views: 456

Answers (1)

vladimir
vladimir

Reputation: 15226

Consider using WITH Clause:

WITH (
  SELECT (MIN(FlightDate), MAX(FlightDate), groupUniqArray(AirlineID)) /* tuple with required aggregates */
  FROM ontime2
  WHERE Origin='JFK'
) AS cte
SELECT
  FlightDate,
  FlightNum,
  Origin,
  Dest,
  AirlineID
FROM
  ontime1
WHERE 
  FlightDate >= cte.1 AND
  FlightDate <= cte.2 AND
  has(cte.3, AirlineID)
LIMIT 10;

Upvotes: 2

Related Questions