The query below takes about 5 sconds to run
select uren.MEDEW_ID as MEDEW_ID, uren.START as UREN_WEEK, sum(uren.DUUR)
as DUUR
from uren as uren
where uren.START >= '2015-05-25'
and uren.START < '2015-06-01'
and *uren.type = 0*
group by uren.MEDEW_ID, UREN_WEEK
When I remove the *uren.type = 0* where clause, the query runs in a few
miliseconds.
When I EXPLAIN the query I see it uses the index on the field TYPE, when I
remove the *uren.type = 0*, the query uses the index on START. Also, when I
remove the index on the field TYPE the query runs fast.
SELECT
UREN.MEDEW_ID AS MEDEW_ID,
UREN.START AS UREN_WEEK,
SUM(UREN.DUUR) AS DUUR
FROM PUBLIC.UREN UREN
/* PUBLIC.UREN_TYPE: TYPE = 0 */
/* scanCount: 632096 */
WHERE (UREN.TYPE = 0)
AND ((UREN.START >= '2015-05-14')
AND (UREN.START < '2015-05-28'))
GROUP BY UREN.MEDEW_ID, PUBLIC.GETWEEKSTRING(UREN.START)
/*
total: 25688
UREN.UREN_DATA read: 22925 (89%)
UREN.UREN_TYPE read: 2763 (10%)
If I am right, the existance of an index is not allways a performance
improvement, in this case it causes the poor performance.
How to recognize such situations, and how to controll them ?
regards,
Rinse
--
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 http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
