[TYPO3-dev] list module hangs at 1 million records

[Franz Holzinger]

Hello,

Dmitry Dulepov [typo3] a écrit :

> Franz Holzinger wrote:
>> Votre requête SQL a été exécutée avec succès (traitement: 149.1525 sec.)
>> requête SQL:
>> ALTER TABLE `tx_myext_table` ADD KEY comp1( pid, deleted( 1 ) , sorting )
>>
>> What is the '(1)' after 'deleted' for? Does this have an advantage?
> 
> Yes. It makes index smaller. deleted field is only 0 or 1 but it can be
> as long as 11 bits in the dastabase. Setting (1) uses only 1 significant
> bit in the index. This is important if table is long: the smaller the
> index, the more chanmces that MySQL will use it instead of doing full
> table scan.

Now I have added another key:

ALTER TABLE `tx_myext_table` ADD KEY comp2( pid, deleted( 1 ) , crdate)


EXPLAIN SELECT *
FROM tx_myext_table
WHERE pid =1
AND deleted =0
ORDER BY crdate
LIMIT 21 , 20


id 	select_type 	table 	type 	possible_keys 	key 	key_len
ref 	rows 	Extra
1 	SIMPLE 	tx_myext_table 	range 	parent,comp1,comp2 	comp2
5 	NULL 	1665195 	Using where

> This looks good because select_type is SIMPLE. This is one of the best
> that you can get.
> 
>> So the comp1 key is used.
And also comp2 key is used here now.

>> So back to the original SQL query with _crdate_:
>>
>> Affichage des enregistrements 0 - 19 (20 total, traitement: 168.3928
>> sec.)
>> requête SQL:
>> SELECT *
>> FROM tx_myext_table
>> WHERE pid =1
>> AND deleted =0
>> ORDER BY crdate DESC
>> LIMIT 21 , 20
>>
>> So it takes now 168 seconds instead of 31.4553 sec. before. This is a
>> slowdown by factor 5 using this additional key.
> 
> That's right because comp1 key does not have crdate. Make another
> similar index but include crdate instead of "sorting" and it will be
> fast! I did not know what sorting you have in that table, this is why I
> used "sorting". But make sure that crdate is the last in the index. It
> is very important to MySQL. Sorting field must be the last in the index.
> 
> It will not help to have both sorting and crdate in one index.

With a separate key com2 it takes only 0.0019 sec instead of 31.4553
seconds before. And the query with ordering by the field 'sorting' did
not at all get slower by adding another key.


Franz