Issue Feature Closed

#16914Optimizing tables to speed up FE

John Angel 01/28/07, 18:33 updated 10/02/18, 12:39

Description

TYPO3 database structure should be optimized to speed up FE.

References:
http://wiki.typo3.org/index.php/Database_optimization#Using_MySQl_indexes_effectively
http://lists.typo3.org/pipermail/typo3-dev/2006-March/016967.html

All FE queries which read from database should be checked and indexes should be introduced where necessary.

Simple setup of indexes may look like these:

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

ALTER TABLE `cache_hash` ADD INDEX ( `hash` ( 4 ) ) ;

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

ALTER TABLE `fe_users` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `fe_users` ADD INDEX ( `disable` ( 1 ) ) ;

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 `pages_language_overlay` ADD INDEX ( `hidden` ( 1 ) ) ;
ALTER TABLE `pages_language_overlay` ADD INDEX ( `sys_language_uid` ) ;

ALTER TABLE `sys_domain` ADD INDEX ( `hidden` ( 1 ) ) ;
ALTER TABLE `sys_domain` ADD INDEX ( `sorting` ) ;

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

ALTER TABLE `sys_language` ADD INDEX ( `hidden` ( 1 ) ) ;

ALTER TABLE `sys_note` ADD INDEX ( `deleted` ( 1 ) ) ;

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

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

(issue imported from #M4875)

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.