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

[Franz Holzinger]

Hello,

ries van Twisk a écrit :

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

8.0252 sec

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

It did not jump back to 30 sec.

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

7.4060 seconds

Yes, the filling of crdate and sorting with different values has
improved the execution speed.

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

The index on crdate and sorting did not help very much, only 0.5 seconds
to 8 seconds former total execution time.
Dmitry's way has proven to be true, because it has reduced the execution
speed by a factor of about 10000.

Franz