Hi,
The issue is that when you want to limit the number of items (last level) by page in navigation tree, you can have an issue with the last group.
Imagine that you set the config to 25 items, you have a first group of 20 DB, and another one of 15 DB. Here, the last group will contain only 5 items and not all the group. But there is no information that there is other children of this group, so you can believe that you see all the items of the group.
I think that we better have to filter on the first level, than on the number of the last level items…
The list of DB could be get by one query:
select s.*
from INFORMATION_SCHEMA.SCHEMATA s,
(
select DB_first_level
from (
SELECT SCHEMA_NAME, SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level
FROM INFORMATION_SCHEMA.SCHEMATA
) t
ORDER BY DB_first_level ASC
LIMIT 1, 3
) t2
where 1 = locate(concat(DB_first_level, '_'), concat(s.SCHEMA_NAME, '_'))
order by s.SCHEMA_NAME;
(Here, I assume that the first level separator is "_" and that we want 3 items of first level.)
(If someone has basics of optimisation and have some idea to improve this query, it could be useful.)
What do you think about removing limit number of last level items and replace it by a limit of first level items?
Or any idea to fix the issue in another way?
Thanks,
Hugues.