Steps to reproduce:

1. Create thousands of hosts
2. Create millions of unique items across the hosts (my deployment has over 27 million unique items
3. Try to use any widget CPatternSelect and experience incredible slowness. The slowness is the worst when leaving out a trailing wildcard * from the pattern. If you do not include any wildcards the jsrpc API call adds leading and trailing wildcards which makes the database query impossibly slow. This makes the user experience really bad
4. change the defines.inc.php constant that limits SVG graphs to only 50 metrics. Though not super critical to reproduce this issue, 50 metrics (when using dataset aggregation) is not a realistic limitation when using Zabbix in large environments. We set it to 10000 which still can provide good performance on advanced hardware.

Expected:
We need a better solution in large enterprise settings

What I've done to mitigate this:

The primary cost appears to be the use of the item_rtname table which has no index for name_resolved or name_resolved_upper. Adding this index provides some improvement, but at 27 million items, the database is still too large and queries can take awfully long.

I've modified jsrpc.php, several widgets and include/classes/db/DB.php to support the use of "key=<key_>" in the CPatternSelect and CWidgetFieldMultiSelectItem. With these modifications a query like this one:

SELECT i.itemid,i.hostid,i.history,i.trends,i.units,i.value_type,COALESCE(irn.name_resolved,'') AS name_resolved,i.name FROM items i LEFT JOIN item_rtname irn ON i.itemid=irn.itemid WHERE i.flags IN (0,4) AND i.hostid=10626 AND ((irn.name_resolved_upper LIKE '%CPU%' ESCAPE '!')) AND i.value_type IN (0,3) ORDER BY i.name LIMIT 50 

can become this:

SELECT i.itemid,i.hostid,i.history,i.trends,i.units,i.value_type,COALESCE(irn.name_resolved,'') AS name_resolved,i.name FROM items i LEFT JOIN item_rtname irn ON i.itemid=irn.itemid WHERE i.flags IN (0,4) AND i.hostid=10627 AND ((i.key_ LIKE 'system.cpu.util%' ESCAPE '!')) AND i.value_type IN (0,3) ORDER BY i.name LIMIT 50; 

On a small scale, the differences are undetectable, but I can assure you in extremely large environments, querying for item patterns by using the key_ field is the only way to obtain any decent performance in dashboards when using them or building them.