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!