[TYPO3-dev] performace of cachingframework

[Bjoern Pedersen]

Hi,

hafter analyzing my slow-query log, it seems that the caching framework
table structure is suboptimal:
Quite a lot of query like:

SELECT identifier FROM cachingframework_cache_hash WHERE crdate +
lifetime < 1297803600 AND lifetime > 0;

or DELETE FROM cachingframework_cache_hash WHERE crdate + lifetime <
1297803600 AND lifetime > 0;


Count: 20  Time=33.48s (669s)  Lock=0.00s (0s)  Rows=0.0 (0),
typo3_frm2[typo3_frm2]@localhost
  DELETE FROM cachingframework_cache_hash WHERE crdate + lifetime < N
AND lifetime > N

Count: 20  Time=55.02s (1100s)  Lock=0.00s (0s)  Rows=0.0 (0),
typo3_frm2[typo3_frm2]@localhost
  DELETE FROM cachingframework_cache_pages WHERE crdate + lifetime < N
AND lifetime > N

Count: 20  Time=17.54s (350s)  Lock=0.00s (0s)  Rows=3401.6 (68032),
typo3_frm2[typo3_frm2]@localhost
  SELECT identifier FROM cachingframework_cache_hash WHERE crdate +
lifetime < N AND lifetime > N

These query require a full table scan.

If we had a  expires column set to crdate+lifetime, an index could be
used, thus speeding up the queries.

Björn