How to improve query performance with about 2 million on cached table?
The desktop is pretty powerful(from memory 4GB memory, 4 core processor, SSD) running hsql server.
- Sql Execution response times: About 50 ~ 70 seconds.
In the query below,
FROM FM_EVENTLOG E, CM_NODE N
WHERE E.OCCURRENCETIMELONG BETWEEN 63507974400000 AND 63510605999000
AND E.NODEID = N.ID
AND N.ISDELETED = 0
This FM_EVENTLOG has OCCURRENCETIMELONG index.
Your index looks correct for the query. Use
EXPLAIN PLAN FOR SELECT COUNT(E.ID) …
to see an index is used for both tables.
If COUNT is returning values that are not very large, the query should complete quickly.
So, It's use the COUNT function in case of how many data?
It is not possible to say exactly. It depends on the indexes and what percentage of rows in the second tables have ISDELETE = 0.
In the best case scenario, it should take a fraction of a second.
Sign up for the SourceForge newsletter:
You seem to have CSS turned off.
Please don't fill out this field.