[Large Databases]: Make tree searches by name more efficient #7752

For collections with exceptionally large taxonomy trees (e.g., ~1 million rows), the taxon search box severely degrades server performance. Every keystroke sends a query using a leading wildcard (LIKE '%pattern%'), which prevents standard index usage and forces a full table scan. In environments hosting multiple collections, a single user typing a scientific name rapidly queues up multiple 5-second scans.

SELECT taxon.TaxonID, IFNULL(taxon.FullName, '') FROM taxon WHERE taxon.TaxonTreeDefID IN (1) AND taxon.FullName LIKE '%hydrolea%' ORDER BY IFNULL(taxon.FullName, '') LIMIT 0, 1000

This consumes disk I/O and starves other collections on the same server, potentially resulting in 502 Gateway Timeouts for other users.

Proposal
To improve performance and reduce server load, searches should fetch a limited number of results per query (for example, limiting the initial fetch to 25 or 50 records instead of 1000) and paginate the remaining results when scrolling. Fetching smaller chunks will reduce the I/O tax per keystroke while still allowing users to find the correct name.

Describe alternatives you've considered
Several other strategies could be employed either alongside or instead of pagination to mitigate this issue:

1. Implement a strict debounce on the input field, waiting 500ms after the user stops typing before firing the query to the backend.
2. Prevent the query from firing until the user has typed at least 3 or 4 characters.
3. Migrate to using full text indexes and MATCH() AGAINST() for substring searches, which can utilize indexes for substring matches and resolve queries in milliseconds instead of seconds.

Reported By
Tomislav, UT Austin on BoldDesk in ticket #616

Additional context
This was identified via slow query logs where the taxon search in a botany collection (PRC) accounted for 72% of all slow queries on the server, causing cascading 502 errors for a vertebrate paleo collection (VPL) sharing the same MariaDB instance.