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

[Ries van Twisk]

Hey marc,


great job.. It took on my site rendering time down from 9000ms to about 
2500ms.

I also... as with many other people started to analyse this but time...
I already have DBAL installed and xclassed it I grab all query and send 
them to a sql parser.
 From there I was planning to analyse what indexes could be set on what 
tables most of the
work is already beeing done and I only need to create a BE module to 
show results and make
a little module that can give hints.

Ries



>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 --->
>_______________________________________________
>TYPO3-dev mailing list
>TYPO3-dev at lists.netfielders.de
>http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev
>  
>


-- 
Ries van Twisk
Freelance Typo3 Developer
=== Private:
email: ries at vantwisk.nl
web:   http://www.rvantwisk.nl/
skype: callto://r.vantwisk
=== Work:
email: ries at livetravelguides.com
web:   http://www.livetravelguides.com