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

[Dmitry Dulepov [typo3]]

Hi!

Franz Holzinger wrote:
> Now I have added another key:
> 
> ALTER TABLE `tx_myext_table` ADD KEY comp2( pid, deleted( 1 ) , crdate)
> 
> 
> EXPLAIN SELECT *
> FROM tx_myext_table
> WHERE pid =1
> AND deleted =0
> ORDER BY crdate
> LIMIT 21 , 20
> 
> 
> id 	select_type 	table 	type 	possible_keys 	key 	key_len
> ref 	rows 	Extra
> 1 	SIMPLE 	tx_myext_table 	range 	parent,comp1,comp2 	comp2
> 5 	NULL 	1665195 	Using where
> 
>> This looks good because select_type is SIMPLE. This is one of the best
>> that you can get.
>>
>>> So the comp1 key is used.
> And also comp2 key is used here now.

Right. pid, deleted and crdate are all in the right order, so comp2 is used.

> With a separate key com2 it takes only 0.0019 sec instead of 31.4553
> seconds before. And the query with ordering by the field 'sorting' did
> not at all get slower by adding another key.

I guess it will use comp1 key.

MySQL is tricky thing when it comes to optimization :) I remember spending a week or so near keyboard and with books around. Experimented a lot but result was worth it :)

-- 
Dmitry Dulepov
TYPO3 Core team
More about TYPO3: http://typo3bloke.net/
Subscribe: http://typo3bloke.net/rss.xml
Latest article: http://typo3bloke.net/post-details/extension_updates_loginusertrack_and_comments/