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

[ries van Twisk]

Hey Franz,

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;

and

SELECT * FROM table 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.

Ries

On Jul 23, 2008, at 1:21 AM, Franz Holzinger wrote:

> ries van Twisk a écrit :
>> hey Franz,
>>
>> properly your mayor problem is the order by.
>>
>> Can you try this??
>>
>> SELECT * FROM table WHERE pid=1 AND deleted=0 LIMIT 21,20;
>>
>> it should be (a lot) faster then the one with a order by.
> Yes, indeed.
> 0.1097 sec.
> But this is with additional index:
> deleted  	 INDEX  	 1   	 deleted
>
> 0.0014 sec
> This is after the deletion of the index deleted. This is strangly much
> faster than with the index.
>
>
> Here is the normal SQL query in phpMyAdmin:
>
> Affichage des enregistrements 0 - 29 (1665226 total, traitement:  
> 0.0014
> sec.)
>
>> Now I don't know the list module very well, but I believe it can  
>> sort by
>> crdate and with the sorting field.
>>
>> Now try this:
>>
>> select crdate FROM largetable group by crdate; -- Let me how may  
>> records
>> you get
>
> Affichage des enregistrements 0 - 29 (31353 total, traitement:  
> 4.5455 sec.)
>
> 31353 records.
>> select sorting FROM largetable group by sorting; -- Let me how may
>> records you get
>
> Affichage des enregistrements 0 - 0 (1 total, traitement: 4.2275 sec.)
>
> 1 record.
>
>
> Franz
> _______________________________________________
> TYPO3-dev mailing list
> TYPO3-dev at lists.netfielders.de
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS  
WebORB PostgreSQL DB-Architect
email: ries at vantwisk.nl
web:   http://www.rvantwisk.nl/
skype: callto://r.vantwisk