How to determine the best index on a table with 3 nullable columns?
When looking up contact info, an index with username, email, mobile_phone_number would be great except for three small problems


uniqueness of username
uniqueness of email
uniqueness of mobile_phone_number


If username is supposed to be unique, then username, email, mobile_phone_number would just be overkill with index bloating.

Since email and mobile_phone_number are nullable, they need unique indexes as well.

If you create a unique index on username, email or mobile_phone_number, MySQL will disallow the insert for you on any individual uniqueness.

You could still create an index username, email, mobile_phone_number as a good covering index to retrieve all three columns when looking up by username with

SELECT username, email, mobile_phone_number
FROM users WHERE username = '...';


SUGGESTION

Create the indexes to have the following

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(81) DEFAULT NULL,
  `mobile_phone_number` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `mobile_phone_number` (`mobile_phone_number`),
  UNIQUE KEY `contact_info_ndx` (`username`,`email`,`mobile_phone_number`)
) ENGINE=InnoDB;


by running

ALTER TABLE users
   ADD UNIQUE KEY (`username`),
   ADD UNIQUE KEY `email` (`email`),
   ADD UNIQUE KEY `mobile_phone_number` (`mobile_phone_number`),
   ADD UNIQUE KEY `contact_info_ndx` (`username`,`email`,`mobile_phone_number`)
;