TYPO3 Core

ALTER TABLE statements are after-installation patch.

For pre-installation, this should be made as a part of appropriate CREATE TABLE statements in t3lib/stddb/tables.sql.

E.g.

CREATE TABLE `be_groups` (
uid int(11) unsigned NOT NULL auto_increment,
….
PRIMARY KEY (`uid`),
KEY `parent` (`pid`),
KEY `deleted` (`deleted`),
KEY `hidden` (`hidden`)
);

ALTER TABLE `be_groups` ADD INDEX ( `deleted` ( 1 ) , `hidden` ( 1 ) ) ;
is better than :
ALTER TABLE `be_groups` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `be_groups` ADD INDEX ( `hidden` ( 1 ) ) ;

The INDEX system is'nt very easy to understand, I think it is a hrad work to optimize all Typo3 tables.
Maybe INDEX should be defined using the TCA conf to find field names (deleted, hidden, starttime, endtime, etc...)

The problem is that Extension Manager does not recognize INDEX statements in 'CREATE TABLE' ....

Right, what is better should be decided using EXPLAIN SELECT statement.

The way how indexes are created is directly related to SELECT statements being executed.

All standard (mostly FE) queries should be processed and based on them we should create appropriate indexes.

enableFields() seems to be a standard addition to WHERE clauses. So we should consider to have an index which improves queries build with enableFields()

Related fields are:
hidden
disabled
starttime
endtime
fe_group
and related versioning columns (anyone familiar with that?)

http://typo3.org/fileadmin/typo3api-4.0.0/db/dd6/class_8t3lib__page_8php-source.html#l00936

Good way of sniffing what's going on exactly is enabling mysql log, by setting right after [mysqld] section in my.ini these 2 lines:
flush
log=query.log

(Note: Not for production enviroment, it slows down mysql!)

This way we can fetch all SELECT queries. Using copy&paste and prefixing it with EXPLAIN we can see what indexes are (not) being used.

Adding inidices hpaharzardly improves nothing. And without checking I can tell that absolutelly no FE query will benefit from the posted inidces. As a rule an index with only one column makes only sense when used as unique key.

To make efficient inidices you must analyze typical queries with EXPLAIN. Aftre that you can expirement with different indices to find the ebst index (for a given query).

+1 for Martin.
Let's move the discussion to the dev-list.

My post about indexes:
http://lists.netfielders.de/pipermail/typo3-dev/2007-March/022345.html

So this bug should be closed then.

If someone wants new indexes, he must provide numbers to prove the speed advantage.

We do not apply the indicated changes because it must be prooved to improve performance