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

[ries van Twisk]

Hey Franz,

I don't know the internals if MySQL but I do know that analyze shows  
very limited what MySQL does internally.

However there is a way to test if sorting and crdate indexes are used:

  1) remove the index on crdate and sorting then re-run the SQL's

if the time jumps up to 30 seconds again (assuming no other indexes  
are created) then crdate and sorting indexes are really used however  
they just don't simply show in the explain.
This is quite common in database world and explain simply shows the  
query plan  but often sorting is done using indexes aswell.

So now you are down to 7 seconds.

I t'hink' your current problem is also this:

The cardinality on pid and deleted are close to zero so that means  
that MySQL HAS to scan
most of the index and/or table means it does have to go through all  
million of rows.
Then comes to the conclusion that he has one million records. Then  
start sorting. Then only returns 20 records. Lot's of time.

To know this is try run this SQL (make sure you have an index on  
crdate and sorting):

SELECT * FROM table ORDER BY crdate LIMIT 21,20;

If the above is fast (<200ms) then pid and deleted are your next  
problem and you need
to figure out for yourself if you can divide your mil records into  
several pages.

What Dmitry says might be true, I don't know MySQL internals well, but  
this compound index
(pid,   deleted, sorting) or (pid, deleted, sorting) might not benefit  
your sorting problem.
since remember cardinality on pid and deleted is close to zero. Then  
sorting is not used to really
sort your table.Just is just a wild guess....

Ries





On Jul 23, 2008, at 9:46 AM, Franz Holzinger wrote:

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