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

[Franz Holzinger]

Dmitry Dulepov [typo3] a écrit :
> Hi!
> 
> Franz Holzinger wrote:
>> Dmitry Dulepov [typo3] a écrit :
>>> May be [missing] indexes is a problem? Try adding composite index on pid
>>> and sorting column (sorting column last!) and see if it helps.
>> There are already keys:
>>
>>  PRIMARY       PRIMARY       1665226 uid
>> parent              INDEX                     pid
>>
>> Now I create an index on deleted.
> 
> This will not help. MySQL uses only one index at a time. It will not use
> "deleted" because pid index has much better identifying probability. You
> need a composite index:
> 
> alter table tx_myext_table add key comp1 (pid,deleted(1),sorting)

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?

> However this index may be too large. If index is too large, mysql will
> not use either. Try making such index and then calling "explain select *
> from tx_myext_table where pid=1 and deleted=0 order by sorting limit
> 21,20". If you do not see that it uses comp1, then you have to drop it
> and recreate without deleted field.

Affichage des enregistrements 0 - 0 (1 total, traitement: 4.7298 sec.)
requête SQL:
EXPLAIN SELECT *
FROM tx_myext_table
WHERE pid =1
AND deleted =0
ORDER BY sorting
LIMIT 21, 20
    	

Textes complets
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	tx_myext_table 	range 	parent,comp1 	comp1 	5 	NULL 	1665224 	

So the comp1 key is used.

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.

However using the _sorting_ field it gets very fast now (about factor of
10000):

Affichage des enregistrements 0 - 19 (20 total, traitement: 0.0019 sec.)
requête SQL:
SELECT *
FROM tx_myext_table
WHERE pid =1
AND deleted =0
ORDER BY sorting DESC
LIMIT 21 , 20


>> This has unfortunately timed out in phpMyAdmin :-( .
> 
> Yes, it definitely will with 1000000 records. However index will be
> created because mysql process does not abort if phpMyAdmin does.
So we cannot stop the already started SQL processes unless with a db
admin tool, even if the PHP script has been stopped.

Franz