Hi all,
To simplify my problem; I have a query on the table containing 35K rows
that takes 3 seconds to execute, looking to make it working faster.
The table has (among others) the fields: ID (primary key) and DATA
(datetime).
Having specific date I want to get the row that has DATA no later than
specified and maximum value of ID:
SELECT id
FROM TRANSAKCJA
WHERE "DATA" <= '2019-01-18 00:00:00.0'
ORDER BY data DESC,id DESC
After running it I just take the first row returned.
EXPLAIN ANALYZE returns:
SELECT
ID
FROM PUBLIC.TRANSAKCJA
/* PUBLIC.DDINDEX: DATA <= TIMESTAMP '2019-01-18 00:00:00.0' */
/* scanCount: 35347 */
WHERE DATA <= TIMESTAMP '2019-01-18 00:00:00.0'
ORDER BY =DATA DESC, 1 DESC
/*
reads: 2721
*/
It uses the index created on DATA field however it is still slow. It scans
whole table.
Any hints how can I make it work fast?
Is there anything like sorted index for DATA that prevents scanning whole
table or something?
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
