[TYPO3-core] Speed up of page cache

[Bernhard Kraft]

Martin Kutschker wrote:

> Generally speaking I don't like statements like "I added index X and now it's faster". I want figures:
> afftected tables, statements, possibly with explains (before and after).

>From tslib/class.tslib_fe.php line 1502:

  function getFromCache_queryRow()  {

    $GLOBALS['TT']->push('Cache Query','');
      $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
        'S.*',
        'cache_pages S,pages P',
        'S.hash='.$GLOBALS['TYPO3_DB']->fullQuoteStr($this->newHash, 'cache_pages').'
          AND S.page_id=P.uid
          AND S.expires > '.intval($GLOBALS['EXEC_TIME']).'
          AND P.deleted=0
          AND P.hidden=0
          AND P.starttime<='.intval($GLOBALS['EXEC_TIME']).'
          AND (P.endtime=0 OR P.endtime>'.intval($GLOBALS['EXEC_TIME']).')'
      );
    $GLOBALS['TT']->pull();


So the fields which are used in the WHERE part of the query are:


cache_pages.hash (indexed)
cache_pages.page_id (indexed)
pages.uid (indexed (PK))
cache_pages.expires (NOT INDEXED !!!)
pages.deleted (NOT INDEXED !!!)
pages.hidden (NOT INDEXED !!!)
pages.starttime (NOT INDEXED !!!)
pages.endtime (NOT INDEXED !!!)


I attached a patch which changes this (make all fields used in this query indexed)


I tested it on a site of me but currently I don't have an large sites available and
the tested one  had only about 500 cache_pages entry ... i didn't see any effects.
(Parsetime was always between 100 and 200 ms)


greets,
Bernhard
-- 
----------------------------------------------------------------------
"Freiheit ist immer auch die Freiheit des Andersdenkenden"
Rosa Luxemburg, 1871 - 1919
----------------------------------------------------------------------
-------------- next part --------------
A non-text attachment was scrubbed...
Name: index_speedup_cache.patch
Type: text/x-patch
Size: 797 bytes
Desc: not available
Url : http://lists.netfielders.de/pipermail/typo3-team-core/attachments/20060117/99ebb576/attachment.bin 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 256 bytes
Desc: OpenPGP digital signature
Url : http://lists.netfielders.de/pipermail/typo3-team-core/attachments/20060117/99ebb576/attachment.pgp