Improve record sorting performance
Currently when TYPO3 inserts a record to a table which is position aware ($GLOBALS['TCA'][$table]['ctrl']['sortby'] is set), it needs to calculate sorting value.
If there is no "sorting space" left between elements, the whole table needs to be resorted.
TYPO3 now does 1 update query per row in the table.
Instead we should make 1 update query to update all rows.
Additionaly the algorithm of calculating the next sorting value should be improved to allow more space between rows == meaning that full resorting will happen less often.
[TASK] Reduce number of needed SQL queries for resorting of records
This reduces the resorting to require only a single update statement
in contrast to "number of records" statements.
It also only updates the records after the current one, so less records
are touched when resorting.
Now handling of the sorting value is following:
- if no record exists: set interval as sorting number
- if inserted before an element: put in the middle of the existing elements
- if inserted behind the last element: add interval to last sorting number
- if collision: move all subsequent records by 2 * interval, insert
new record with sorting = collision + interval
Before, in case of the collision all the records from given pid were
resorted. The first item was getting 512 (2*sortIntervals) as sorting,
following records were getting value of
previous record + sortIntervals (768,1024,...).
I case we have multiple records with the same sorting value,
resorting will not fix that, but just move them up.
New algorithm drawback:
Sorting column values will grow quicker in time as we're always
increasing the sorting value and never tidying the whole table
(thus possibly lowering the max used value).
The current implementation still allows to insert some 80 million
records per colpos if sizeof(int) is only 32bit.
A lot more if we have 64bit.
Changes to the DataHandler->getSortNumber method are mostly comments and
formatting (added guard clause), no change in behavior except for
additional sorting by uid. The additional sorting was required to have
the same test results across database servers.
Description of updated tests:
Now copied tx_irretutorial_1nff_price records have the same
sorting values/order as original records (1,2,3 instead of 1,3,2).
So workspace actions result in the same values as live operations.
Order of the records in both languages is kept (before and after the patch),
It might look wrong that a record 2.5 in translation is between 1 and 2
and not between 2 and 3, but keep in mind that colpos is taken into account.
So record 2.5 has just to be after record 1 (only the two are in colpos 0).
Order of the records is kept, less records have changed sorting values,
as now we're just resorting records after the current one.
now existing page sorting is not changed when importing data.
in CSV relation we don't care about the child sorting field as
the order is determined by the value of the CSV field.
Tested-by: TYPO3com <firstname.lastname@example.org>
Reviewed-by: Markus Klein <email@example.com>
Tested-by: Markus Klein <firstname.lastname@example.org>
Reviewed-by: Anja Leichsenring <email@example.com>
Tested-by: Anja Leichsenring <firstname.lastname@example.org>
Reviewed-by: Christian Kuhn <email@example.com>
Tested-by: Christian Kuhn <firstname.lastname@example.org>