How to change query for make it more quick?

It is some query to MySQL db. It is works (historically) but are very bottleneck I suppose.

SELECT
tb_real.One,
tb_real.Two,
tb_real.TimestampMs,
tb_real.Symbol,
tb_max.MaxTimestapmMs
FROM (SELECT
Symbol,
MAX(TimestampMs) AS MaxTimestapmMs
FROM times
WHERE Symbol = inSymbol
AND TimestampMs < inDateTo) AS tb_max
JOIN (SELECT
TimestampMs,
One,
Two,
Symbol
FROM Times
WHERE Symbol = inSymbol
AND TimestampMs < inDateTo) AS tb_real
ON tb_max.MaxTimestapmMs = tb_real.TimestampMs
;


It's works but slowly enough. Is it possible to make it simplequicker?

1 Answer
1

It seems that the first subquery always selects only one row as soon as Symbol = inSymbol so you can just rewrite it as follow. And sure you have to index Symbol and TimestapmMs fields

Symbol = inSymbol

Symbol

TimestapmMs

SELECT
One,
Two,
TimestampMs,
Symbol,
TimestapmMs
FROM Times
WHERE Symbol = inSymbol
AND TimestampMs = (SELECT
MAX(TimestampMs)
FROM times
WHERE Symbol = inSymbol
AND TimestampMs < inDateTo)


Also if TimestampMs field is unique (Only one record has the MAX value) and this query always returns only one record you can use LIMIT 1 to get this record:

TimestampMs

LIMIT 1

SELECT
One,
Two,
TimestampMs,
Symbol,
TimestapmMs
FROM Times
WHERE Symbol = inSymbol
AND TimestampMs < inDateTo
ORDER BY TimestampMs DESC
LIMIT 1


sp_get_times

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.