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

[Dmitry Dulepov [typo3]]

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)

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.

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

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