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

[Ernesto Baschny [cron IT]]

Hi!

Marc Wöhlken schrieb am 23.03.2006 18:32:

> 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.

Do you have any statistics on what percentage of processing time is
spend in the SQL-queries? If we are talking about <2%, then I would
understand why noone has yet tried improving *that* part, since there
are probably other "chunks" that are better candidates for optimizing.

> 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!

In my tests, I see no difference in the "parse-time" (shown by TYPO3 at
the bottom of the page, if debug is on) when calling some typo3-page
with and without those indexes with "&no_cache=1".

But in your case you should see a reduction of 122ms in parse-time, right?

> 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.

What about adding some compound indices, for example for "hidden,
starttime, endtime, fe_group" fields? Those are almost always used
together, it might speed up selection of records to be shown a bit.


Cheers,
Ernesto


-- 
cron IT GmbH
Dipl.-Inf. Ernesto Baschny
Nobelstr. 15, D-70569 Stuttgart

Tel: +49 711 6868 909-33
Fax: +49 711 6868 909-10

Fingerprint: E918 7A64 9C50 CD35 67A9  752C 1C70 EE8B 915F 6438