[TYPO3-mvc] Extbase/FLOW3 Performance

[Stefan Frömken]

Hello MVC-Team,

I'm not here to discuss about Extbase is slow. There are enough post in 
the internet discussing about that. Further I know...you're working on it...
We have just started a new project in Extbase/Fluid with over 1,5 
million records. I have never worked with such a mass of records. That's 
way I have read many MySQL-Performance documentations in the internet 
the last weeks.
There are pretty cool settings with MySQL and in my last 6 hours I have 
restarted my MySQL-Server over a houndreds of time. The result? Do you 
want to know the result? It's easy: You got a millisecond here and a 
millisecond there. OK...sometimes I got 10 milliseconds. But in most 
cases the problem is not the database. It's the way how to create a query!

Here is a simple query:

SELECT SUM(nettobetrag) AS nettobetrag FROM myMillionRecordsTable WHERE 
((beleg_datum >= '1293836400' AND beleg_datum <= '1306879200') AND k_nr 
= '1610280') AND pid IN (0);

This query needs 3.6 seconds. But I have to execute similar queries with 
different dates over a houndred times for one view. What do you think 
how long the customer have to wait? Too long.

Creating an Index over k_nr and beleg_datum gives me the result within 6 
Milliseconds. This is much better.
Next try: Creating an Index over k_nr, beleg_datum and nettosumme 
results in 2ms.
Next try: Creating an Index over pid, k_nr, beleg_datum and nettosumme 
results in 0.3ms.

You see: Indexing is very importend, but not new to you. So I will show 
you something more interesting. This is only a good example...not for 
real life:

SELECT * FROM tt_content WHERE sys_language_uid=0 ORDER BY l18n_parent;

EXPLAIN will tell you "Using where; Using filesort". tt_content has 4 
indexes and there are indexes for sys_language as for l18n_parent but 
possible_keys keeps beeing empty.OK...let's try it a second time:

SELECT uid, l18n_parent, sys_language_uid from tt_content where 
sys_language_uid = 0 order by l18n_parent;

Shit: EXPLAIN shows as the same as above. OK...try it a last time:

SELECT l18n_parent, sys_language_uid from tt_content where 
sys_language_uid = 0 order by l18n_parent;

Now EXPLAIN tolds us key=language and "Using where; using index". This 
is pretty importend for Extbase and its system. Extbase always works 
with "SELECT *" and there is no easy possibility to reduce this query to 
less fields for better performance.

For now I have created my own class which extends your storage backend. 
I can define my own fields and my view with over houndreds of queries 
was executed within 2.8 seconds. In the beginning of this project with a 
smaller amount of records the view was finnished in 65seconds.

Indexes are importend and in my opinion you should give the customer the 
possibility to make use of their indexes.

Stefan