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

[Dmitry Dulepov]

Hi!

Dimitri Tarassenko wrote:
> 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

EXPLAIN is not precise. This is a prognosis - what mysql _thinks_ he
will do with this query.

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

Not really. I have a forum on one site with a large traffic (at least
300000 hits daily only to PHP pages). PHP code is well optimized, there
is a very little amount of queries, all recordsets are closed to free
memory, etc. Still by tuning mysql parameters I could increase
performance a lot.

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

Same for mysql. Recently performance of realurl was greatly improved
this way.

Dmitry.
-- 
"It is our choices, that show what we truly are,
far more than our abilities." (A.P.W.B.D.)