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

[ries van Twisk]

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.

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
select sorting FROM largetable group by sorting; -- Let me how may  
records you get


(My best guess is you don't get a lot of records back )

Will answer again after you responded....

Ries






On Jul 22, 2008, at 3:28 PM, Franz Holzinger wrote:

> Dmitry Dulepov [typo3] a écrit :
>
>> A quick question: how long does it take to run a query on such a long
>> table with "WHERE pid=1 AND deleted=0 ORDER BY crdate DESC LIMIT  
>> 21,20"?
>
> 31.4553 sec. on a dual core AMD Athlon 3700 with phpMyAdmin.
>
>> 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 has unfortunately timed out in phpMyAdmin :-( .
> So you cannot even create an index afterwards with this tool.
>
> 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