The tags table is enormously slow when there are thousands of tags / records. Any extension that makes use of tags and does a query that lists all the tags, has a huge query time in the admin or front end editing.

Steps to reproduce the issue

Populate the tags table with a huge number of tags. (In my case I did a migration of 5800+ K2 tags)
Go in the admin to Content > articles and notice how long it takes to render the page. Also new articles, banner categories, news feeds, contacts etc. are slow.

Expected result

Redering the page as fast as every other page in Joomla

Actual result

Several seconds loading, before the page is shown.

System information (as much as possible)

It is this query that is query that has a query time of 4989.97 ms

SELECT DISTINCT a.id AS value, a.path, a.title AS text, a.level, a.published, a.lft

FROM ck8jn_tags AS a

LEFT JOIN ck8jn_tags AS b
ON a.lft > b.lft
AND a.rgt < b.rgt

WHERE a.lft > 0
AND a.published IN (0,1)

ORDER BY a.lft ASC

Additionally the explanation in the debugger states:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE a NULL ALL tag_idx,idx_left_right NO INDEX KEY COULD BE USED NULL NULL 5671 100.00 Using where; Using temporary; Using filesort
1 SIMPLE b NULL ALL idx_left_right NO INDEX KEY COULD BE USED NULL NULL 5671 100.00 Range checked for each record (index map: 0x20); Distinct

Additional comments