Designing a user table, FName LName v.s Username I did some research on different ways of getting user's name. Some database designers prefer to have a username that contains first name and last name with space separator, and some other designers have it in two separate fields such as fname and lname. When it comes to searching using mySql, which approach makes it easier to search for a user based on his full name, or only either his last name or first name ? Any advice on the design approach ? My application is probably going to have about 5,000,000 users registered, I'm a bit concerned about in what way to approach user full name.

This is how I'm doing it, assuming that the user enter "alex coner" in one input textbox and press enter, then I parse the string based on space separation and then run the query like this which doesn't know which one is fname and lname : 

select * from User 
where UserFName in ("alex", "coner") 
   OR UserLName in ("alex", "coner") 


is there a way to run this and then get the one that matches the closest ? Assuming there is these users as below:

alex buche
coner johnson
alex coner
david rudd

and the result appears as :

alex coner
alex buche
coner johnson
First of all full text searches are often slower than searching on indexed columns.  But full text searches offer some semantics that column searches do not.  However, full text search is primarily valuable in larger sets of text, such as indexing documents, or when the likely order of the text is fluid. (e.g. John Johnson; Johnson, John; John, Johnson; etc)

In this constrained task of matching names, I would choose the two columns, both indexed, as likely the better performing option.  (But testing is the only way to know for sure.)

If you are using standard indexed columns, then you can quickly search the fname column and/or the lname column and when needed join them on a matching identifier. This is more accurate than a full text search, because the search is very bounded.  

This does require that the search query be made knowing which name is the fname and the lname, of course, or adding additional conditions to check both ways.  (Not always so simple in a multi-language world.)

The full text search syntax will support "phrases" (words in a certain order) or the softer version of words in any order.  In the latter case 'John Johnson' would be satisified by either or both of those names in any order.  

(Of course, for the exact search syntax options see the mySql documentation.)