Any Better Alternative to Get Latest Record Without Limits
I have the following request
DECLARE lv_Duration INT;
SET @lv_Duration = 0;
SELECT @lv_Duration := TIMESTAMPDIFF(SECOND, changedon,NOW())
FROM `transactionhistory`
WHERE transaction_Id = TRIM(_transaction)
ORDER BY tsh_id DESC
LIMIT 1;
In which I get the time since the last write of the transaction_Id fields. But it takes 0.25 seconds on a medium table. My primary autogrowth field is tsh_id. I have an index on the transaction_Id field. I think that ordering and accepting the last record might have a performance impact. so what is any alternative for this?
+3
source to share
1 answer
From the ORDER BY
optimization link provided by McAdam331. I believe your request follows the pattern:
SELECT *
FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;
The composite index (transaction_Id,tsh_id)
shouldn't speed it up.
I don't think there is a faster way than LIMIT
to get the latest entry.
+1
source to share