I have a query that always must return a single row and its execution time is about 5s, so I need to optimize it:
SELECT * FROM RESPONSE res
INNER JOIN REQUEST req ON res.ID = req.ID
WHERE res.FIELD_47 = ?
AND req.CREATE_DATE = ( SELECT max(req2.CREATE_DATE) FROM RESPONSE r2
INNER JOIN REQUEST req2 ON r2.ID = req2.ID
WHERE r2.FIELD_47 = ? );
All present columns have a index and my db is transactional. Please suggest me. Thank you!