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

[Martin Kutschker]

Franz Holzinger schrieb:
> Hello Ries,
> 
> ries van Twisk a écrit :
> 
>> So you problem is the sorting function that TYPO3 adds.
>>
>> Since the cardinality is so low on your table, MySQL almost needs to do
>> a full index scan to find the 20 records.
>> And specially for sorting it needs to go through all 1 mil records.
>>
>> First let's speed up using the sorting field. Do something like this:
>>
>> update YOURLARGETABLE set sorting=uid;
>>
>> That will create a unique entry for each sorting field based on the uid
>>
>> Also do the same for crdate, but like this:
>> update YOURLARGETABLE set crdate= 1216817772  + uid;
>>
>> Then create two indexes. One on crdate and one on sorting.

I think that Dmitry has made it clear that Mysql uses only ONE index for
a given querie (without subqueries). So adding indices for indviudal
fields won't do any good for most situations. He has also explained haow
to create compound indices.
> 
>> SELECT * FROM table WHERE pid=1 AND deleted=0 ORDER BY sorting LIMIT 21,20;
> 
> Affichage des enregistrements 0 - 19 (20 total, traitement: 7.4448 sec.)
> requête SQL:
> SELECT *
> FROM `largetable`
> WHERE pid =1
> AND deleted =0
> ORDER BY sorting
> LIMIT 21 , 20
> 
>> What you should see is that the above two SQL's run a lot faster now.
> 
> It is about 7 seconds now instead of 31 seconds we had before. It has
> got 4-5 times faster than before.
> 
> With EXPLAIN this gives:
> 
> 
> 
> id 	select_type 	table 	type 	possible_keys 	key 	key_len ref 	rows 	Extra
> 1 	SIMPLE 	tx_largetable 	range 	parent 	parent 	4  NULL 	1665224 	Using
> where; Using filesort
> 
> It has only used the key 'parent', but it did not use the 2 additional
> keys 'crdate' and 'sorting'.

AFAIR Dmitry suggested unsing an index  (pid,deleted, sorting) for this
query.

If you make a shadow table for the deleted items you can get rid of the
deleted field. This will save you some extra time.

Masi