TYPO3 Core

Query:

SELECT a.uid AS uid FROM tx_extensionmanager_domain_model_extension a LEFT JOIN tx_extensionmanager_domain_model_extension b ON a.repository = b.repository AND a.extension_key = b.extension_key AND a.integer_version < b.integer_version WHERE a.repository = 1 AND b.extension_key IS NULL ORDER BY a.uid

Current indexes:

PRIMARY KEY (uid),
  KEY index_extrepo (extension_key,repository),
  KEY index_versionrepo (integer_version,repository),
  KEY index_currentversions (current_version,review_state),
  UNIQUE versionextrepo (extension_key,version,repository)

Suggested indexes including index for query above:

PRIMARY KEY (uid),
  KEY index_extrepo (extension_key,repository),
  KEY index_versionrepo (integer_version,repository),
  KEY index_versionrepoext (integer_version,repository,extension_key),
  KEY index_currentversions (current_version,review_state),
  UNIQUE versionextrepo (extension_key,version,repository)