[TYPO3-core] RFC #13945: Bug: Missing key on l18n parent in tt_content table

[Christian Kuhn]

Hey.

Dmitry Dulepov wrote:
> Christian Kuhn wrote:
>> Problem:
>> t3lib_BEfunc::translationCount issues a query like 'SELECT COUNT(*) FROM
>> tt_content WHERE l18n_parent=123 AND sys_language_uid!=0 AND deleted=0'.
>> EXPLAIN shows no usable key for this type of query.
>>
>> Solution:
>> Add key on l18n_parent.
> 
> Have you tried a composite key instead of a single field key? Composite
> keys often give better results.

Here are the BE queries I found related to the l18n_parent fields, the 
FE probably similar queries, but I didn't check this:

SELECT fields FROM tt_content WHERE l18n_parent=123 AND 
sys_language_uid!=0 AND deleted=0

SELECT fields FROM tt_content WHERE l18n_parent=123 AND pid=605 AND 
sys_language_uid>0 AND tt_content.deleted=0

So both use l18n_parent and sys_language_uid, but the second query 
already falls back to a key on pid if no key for l18n_parent is defined. 
It might depend on the current db and overlay structure of a specific 
TYPO3 instance, we should imho let the mysql optimizer decide which 
index should be used.

A combined index (l18n_parent + sys_language_uid) might make sense here.
I discard the deleted field as it doesn't reduce the result set very 
much, previous tests showed that a key on deleted and hidden fields 
usually don't make much sense: The result set after full combined index 
on l18n and sys_lang is nearly identical and small enough to accept the 
"using where" for the rest of the fields.

Attached v2.

Thanks
Christian
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 13945_02.diff
Type: text/x-patch
Size: 407 bytes
Desc: not available
URL: <http://lists.typo3.org/pipermail/typo3-team-core/attachments/20100329/e8ba45a7/attachment.bin>