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

[Dmitry Dulepov [typo3]]

Hi!

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.

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

This looks good because select_type is SIMPLE. This is one of the best that you can get.

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

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.

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

Normally not. You can try to go to "Processes" in phpMyAdmin and kill that process but often it continues to run for some more time. And if it is "alter" or "analyze" or any other similar process, it cannot be killed.

"Process" in MySQL is a thread that runs query.

-- 
Dmitry Dulepov
TYPO3 Core team
More about TYPO3: http://typo3bloke.net/
Subscribe: http://typo3bloke.net/rss.xml
Latest article: http://typo3bloke.net/post-details/howto_use_frontend_user_data_in_a_typo3_mailform/