I have been trying to find a way to optimize this query by probably removing the subquery in where clause.
it currently takes 2 minutes to run this query with the where clause and 4 seconds without the where clause.
SELECT rateString, currency FROM myTable.trades ct WHERE ct.id IN ( SELECT top 1 id FROM myTable.trades tt WHERE tt.currency = ct.currency AND tt.isExecuted = 1 AND tt.status LIKE 'executed' AND tt.transactionType LIKE 'sell' ORDER BY tt.created_at DESC ) ORDER BY currency, created_at DESC
The subquery will limit the result to the top 1 row per currency with some more where and order clause.
Everything is from a single table and no foreign key is involved