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

[Franz Holzinger]

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.
> 
> Repeat these queries:
> 
> SELECT * FROM table WHERE pid=1 AND deleted=0 ORDER BY crdate LIMIT 21,20;

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


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

Franz