MySQL BETWEEN command – performance (order)

Suggestion 1

How about (untested)

SELECT designation, count(*)
  FROM designations 
 GROUP BY designation ORDER BY COUNT(*) DESC LIMIT 9

UNION SELECT "Others" designation, COUNT(*) 
        FROM designations
       WHERE designation NOT IN (
               SELECT designation
               FROM designations 
               GROUP BY designation 
               ORDER BY COUNT(*) DESC 
               LIMIT 9 )

Suggestion 2

This one is tested. It uses MySQL-specific variables. It's efficient.

SET @n=0;

SELECT IF(row_number<10, designation,'Others') name,
       sum(c) AS occurances
FROM
  (SELECT designation, c, @n:=@n+1 row_number
   FROM
     (SELECT designation,
             count(*) c
      FROM designations
      GROUP BY designation
      ORDER BY count(*) DESC) t1) t2
GROUP BY IF(row_number<10,row_number,10);

I am sure that you can improve performance using indices on foreign keys i.e put an index on the accountholderid when you use it as foreign key. It's the place where you would usually put them. And you might put index on LastModificationTImestamp with desc order
CREATE INDEX accountholder_LastModificationTimestamp_idx ON acountholder(LastModificationTimestamp DESC NULLS LAST);
Since you are using top 5000 entries indexing is not actually good thing. And index maintenance is expensive. You will be faster by doing sequential scan. ;) If you use timestamps in your search queries that use index, otherwise not.

Useful article: PostgreSQL Indexes

And if you often hit the same query without the data being updated you can have the materialized view:
Materialized views in PostgreSQL docs

I would put not changing things into materialized view (you can refresh it when need it). So you will have less joins, and when playing with it use explain (planning) or explain analyize (planning and executing). Use analyze so you update your statistics.

After looking at the diagram I would do the following:

Extract the changing part of the AcountHolder into separate table with 1:1 relationship, and leave Revision history into separate table (because there is a last modification timestamp and refreshing materialized view is very expensive) and put everything else into materialized view.