[Typo3-dev] Trivial scalability test

[Kasper Skårhøj]

I'm reminded about other interesting things which questions the validity
of a "simple" test like dans. 

On TYPO3.org I have a storage page. On this page all users, extension
keys, extension repository records, categories, languages etc. are
stored. When using the Web>List module it takes ages to display this
list. Although the amount of HTML used in the backend list is very large
this is not the problem. The problem is the SQL!
It is no problem having 10000+ users.
It is a problem having 2000 records which contains versions of
extensions! Why the difference? (Probably) because the selection of
records from the table of records containing repository versions has a
blob field in which the extensions are stored. This table is in total
some hundred megabytes. Each time I'm listing with the Web>List module
it asks "SELECT .... WHERE starttime blablabla AND endtime blablabla" -
and only the uid and pid fields are indexed! This it makes a full table
sweep, going through hundreds of mega-bytes of content just to find
starttime and endtime fields.
The same is the case many other places in TYPO3. The pages table and the
tt_content table could probably be accused for the same!
The point is; Listing and selecting from a table with 1 million records
is no big deal if the table in total is a few megabytes large (like the
cross table in indexed-search extension is...) - it seems to be highly
dependant on the total size of the database table.

Now, of course this is all bad database design. Probably someone have
been sitting vibrant on the edge of his chair to enter the discussion
now with a comment about indexing. Because with proper indexing all
trivial selection of record from database tables in TYPO3 (using the
standard enableFields() and deleteClause() functions) could be done MUCH
MUCH faster. I would estimate that 95% of all database access in TYPO3
does not use indexes properly - yet!

So, why don't we just add indexes? Well, I actually suggest that we do.
By the DBAL layer it has never been easier even! (because all queries
goes through one API where we can easily append "EXPLAIN SELECT..." to
get statistics and find all slow queries!)

There are two questions remaining:

First; What is the penalty we pay by adding indexes? They are not for
free. Each time we make UPDATE/INSERT or DELETE all indexes are modified
by MySQL as well, so we have to analyse what balance is right for us! I
have NO clue how great the penalty is!?

Second; This task belongs under the profiling project. Is it possible
that this mail could animate anyone to start systematic analysis of the
benefits and drawbacks of adding indexes on tables in TYPO3 (including
what indexes on what tables).

- kasper