Hi i am looking for the most performant way to ensure a unique username. I did already check similar questions but none of them made me happy. So here I came up with my solution. I appreciate your comments.
CREATE TABLE IF NOT EXISTS `user` (
`guid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`firstname` varchar(48) NOT NULL,
`lastname` varchar(48) NOT NULL,
`username` varchar(128) NOT NULL,
`unique_username` varchar(128) NOT NULL,
PRIMARY KEY (`guid`),
KEY `firstname` (`firstname`),
KEY `lastname` (`lastname`),
KEY `username` (`username`),
UNIQUE KEY `unique_username` (`unique_username`),
UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
username contains firstname.lastname without numeric suffix while unique_username contains firstname.lastname.(count of equal usernames)
to get the count of equal usernames I am performing following query against the user table (in advance to the insert).
SELECT COUNT(*) FROM user WHERE username = 'username'
Unfortunately I can't use a lookup against firstname and lastname since they are case sensitive.
The docs say “nonbinary strings (CHAR, VARCHAR, TEXT), string searches use the collation of the comparison operands… nonbinary string comparisons are case insensitive by default”, so you should be able to do this:
SELECT COUNT(*) FROM user WHERE CONCAT_WS('.', `firstname`, `lastname`) = 'username`