I have a username column in my table: username VARCHAR(50) I want to index this column, so I can find a username easily and send messages to users like: "this username has already been taken." So what is the best index plan. the table use innodb engine. Can I use fulltext index?
Asked
Active
Viewed 1,509 times
2
cool
- 165
- 7
1 Answers
4
Enforcing username uniqueness
If you want to enforce uniqueness for the values stored in your username column and you also have a user_id column set up as the PRIMARY KEY (PK) of the relevant table, you might be interested in the implementation of a
for the username column.
Such situation implies that, at the logical level, the username would be an ALTERNATE KEY (AK). An AK is a column, or a combination of columns, that holds values that uniquely identify a certan row but was not chosen as the PK of the pertinent table; each table can have zero, one or more AKs. As suggested above, they are usually defined in a SQL DDL structure via a UNIQUE constraint which is served, at the physical level, by a UNIQUE index.
Logical and physical levels
As it is paramount to distinguish logical from physical aspects when constructing a relational database —independently of the platform of choice—, you might find the concepts and links included in this answer of help.
Displaying a warning
If you want to display a message to the people trying to enter username values that have already been taken, you should develop such functionality in the code of the application programs that have access to your database.
Setting up the length of a column
If you don’t want to allow username values with a length greater than 18 characters, then you might like to define the column size accordingly, i.e., VARCHAR(18), so you may find of value the following content from the MYSQL reference manual about
from which I will stand out the following paragraph:
The
CHARandVARCHARtypes are declared with a length that indicates the maximum number of characters you want to store. For example,CHAR(30)can hold up to 30 characters.
Prefix and Fulltext indexes
@Ypercube has enriched this answer with responses to your individual questions about some types of indexes:
Can I use Prefix index?
Technically you could but there is no reason for that. Prefix indexes are rarely - or never - useful in MySQL. If you want to restrict the length of the usernames to 18, the column should be VARCHAR(18) and not (50). If you allow only ASCII characters in the usernames, the column should have an ascii character set and not UTF-8. And even if you keep the VARCHAR(50), the index should still be in the whole column, not a prefix.
What about a Full Text index?
No, you don’t need them either. Full text indexes are for when one wants to search words or phrases in large texts. Not useful in your case. A simple (unique) B-tree index would be the best.
