XWIKI-13468 we noticed that the document tree was very slow on MySql when expanding a tree node with 5k child nodes. This is the SQL query that is executed on the database:
Initially we thought the query is slow only because of the complex order by clause but it turns out that it is slow also when performing pagination without sorting. The first page is loaded very fast (200ms without sorting) but the query becomes slower and slower as we increase the offset (3s on the 5th page). The query takes at least 32s when sorting by title.
The slowness seems to be caused by the fact that we join and apply constraints to a temporary table which is the result of a union. Temporary tables don't have indexes and thus retrieving the rows is very slow (compared to a real table). The recommended solution is to move the joins and constrains inside the union, even if this means duplicating them for each of the unified selects.
What's strange is that HSQLDB, Oracle and PostgreSql don't have this problem. It seems that MySql's query optimizer is not as good as theirs (it doesn't know how to re-write the query so that the joins and constrains are inside the union).