[TYPO3-dev] Typo3 Database design question (optimization / indices)

[Marc Wöhlken]

Hi!
I just wondered why the Typo3 database tables use only so few index fileds.
Does this have any special meaning?

I just did a little benchmarking on my local webserver and found that by
adding a few simple index fileds I could speed up the overall database
processing time by a factor of about 300.

This seems to be quite a significant gain in performance even if you
leave out that overall page generation does require more that just
database queries.

Benchmarking:

1. I modified t3lib/class.t3lib_db.php to get some information about
numer of db queries, used tables and the time they took. For a detailed
code listing see php-code provided at bottom.


Doing some measurements I found that the average db processing time on
my system was 0.122s. this seems quite fast but wait until we see what
index fileds can do for us ;-)

2. I modified a few of the Typo3-Database tables to make use of refinded
indices. For details see SQl-statements below

Than I remeasured the db processing time and found it to be remarkably
faster: 0.0004s on average!

This corresponds to a 1/300 of tehn time it took without the newly added
index fileds.


CONCLUSION:
Typo3 can benefit from well chosen index fileds. how well it will use
index fields is not clear as the results on my machine are not
representative - further measurements would be necessary.

QUESTION:
Why doesn't typo3 use these fields?!

Hoping for some interesting discussion
regards

	Marc (aka maxhb)



<!-- SQL START -->
ALTER TABLE `cache_hash` ADD INDEX ( `hash` ( 4 ) ) ;

ALTER TABLE `pages` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `pages` ADD INDEX ( `hidden` ( 1 ) ) ;
ALTER TABLE `pages` ADD INDEX ( `doktype` );
ALTER TABLE `pages` ADD INDEX ( `sorting` );

ALTER TABLE `tt_content` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `tt_content` ADD INDEX ( `hidden` ( 1 ) ) ;

ALTER TABLE `sys_template` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `sys_template` ADD INDEX ( `hidden` ( 1 ) ) ;
ALTER TABLE `sys_template` ADD INDEX ( `sorting` );
<!-- SQL END -->



<!--- t3lib/class.t3lib_db.php MODSTART --->

   function
exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')
   {
        // Start
        static $total_time = 0;
        $logfile = '/tmp/max/typo3.log';

        if($total_time == 0) {
            $message = str_repeat('-',80) . "\n";
        } else {
            $message = '';
        }
        $message .= str_replace("\n","
",$this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit));
        $message = preg_replace("/(\s+)/"," ",$message);
        if(!function_exists('getmicrotime')) {
            function getmicrotime() {
            list($usec, $sec) = explode(" ",microtime());
            return ((float)$usec + (float)$sec);
            }
        }

        $start = getmicrotime();
        // Ende

        $res =
mysql_query($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit),
$this->link);
        if ($this->debugOutput)    $this->debug('exec_SELECTquery');

        // Start
        $ende = getmicrotime();

        $time = $ende - $start;
        $total_time += $time;

        $message = $time . " (" . $total_time . ")\n" . $message . "\n";
        error_log($message,3,$logfile);
        // Ende
        return $res;
    }
<!--- t3lib/class.t3lib_db.php MODSTART --->