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

[Dimitri Tarassenko]

Marc,

First of all - you did a GREAT job, something I long wanted to do but
never had time.

On 3/23/06, Marc Wöhlken <woehlken at quadracom.de> wrote:

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

I think this needs to be done in a slightly different way, though.
While you are measuring the performance in TYPO3, there are much
better and more precise profiling tools in MySQL that discard the time
spent in PHP-MySQL, etc., and measure net gain inside the mysql
server. I am talking about EXPLAIN and query-logging. If you haven't
found it already, 
http://dev.mysql.com/tech-resources/articles/pro-mysql-ch6.pdf

> QUESTION:
> Why doesn't typo3 use these fields?!

You probably meant "indexes", not fields. Anyways, very good question.
Probably because for ages the biggest performance issue was not within
MySQL but with PHP, and the majority of performance-improving efforts
went into caching. MySQL tuning was typically done just by enabling
query caching - when most of your time is spent on repeating the same
query, query caching may give you better performance boost than
indexing.

One other observation - I know very little of MySQL performance
tuning, but both on MS SQL and Postgres it is sometimes better to have
"compound" indices including 2 or more fields. For example, a query

select a,b,c from t where a=1 and b>23;

will work faster if there is a compound index including a & b, versus
the case when there are 2 separate indices for a and b. This is more
noticeable on larger datasets, though. What I am trying to say is that
it might be better to look again at the most time consuming queries
and custom-tailor compound indices for them, rather than just index
all fields separately.

Anyways, great job!

--
Dimitri Tarassenko