At the moment the database table phabricator_user.user has not an index to cover the column profileImagePHID. This may be a problem since we may have at least 1 query that needs to find users using a specific profile image (T16074).
Current table definition:
CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `phid` varbinary(64) NOT NULL, `userName` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `realName` varchar(128) NOT NULL, `dateCreated` int(10) unsigned NOT NULL, `dateModified` int(10) unsigned NOT NULL, `profileImagePHID` varbinary(64) DEFAULT NULL, `conduitCertificate` varchar(255) NOT NULL, `isSystemAgent` tinyint(1) NOT NULL DEFAULT 0, `isDisabled` tinyint(1) NOT NULL, `isAdmin` tinyint(1) NOT NULL, `isEmailVerified` int(10) unsigned NOT NULL, `isApproved` int(10) unsigned NOT NULL, `accountSecret` binary(64) NOT NULL, `isEnrolledInMultiFactor` tinyint(1) NOT NULL DEFAULT 0, `availabilityCache` varchar(255) DEFAULT NULL, `availabilityCacheTTL` int(10) unsigned DEFAULT NULL, `isMailingList` tinyint(1) NOT NULL, `defaultProfileImagePHID` varbinary(64) DEFAULT NULL, `defaultProfileImageVersion` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `userName` (`userName`), UNIQUE KEY `phid` (`phid`), KEY `realName` (`realName`), KEY `key_approved` (`isApproved`) ) ENGINE=InnoDB
So basically the proposal is to:
ALTER TABLE `phabricator_user.user` ADD KEY `profileImagePHID` (`profileImagePHID`);
Benefits:
- adding an index on that column would speedup (rare) queries (T16074) affecting that column, to avoid a full table scan. This may be desired by large installations with more than 500K users (?). This performance benefit could be noticed by huge installations with more than 1-2 million users (?).
Unrelevant benefits but good to know:
- this is something that makes semantic sense in InnoDB since normally this should be considered a foreign key, and all foreign keys in InnoDB have an index.
Downsides:
- it would increase the database weight of the user table, for large installations (by how much?) for an unclear benefit