If I want to do a check a name, I want to see how many rows/name exists in the "username" column under users table. Lets say thousands ... hundred of thousands, should I use:
count(name),
count(*) or
SELECT username FROM users where username = 'name'
Which one is the more appropriate? Or they will give same result in term of speed/response?
COUNT(*) and COUNT(Name) might produce different values. COUNT will not include NULL values, so if there are any instances of Name that equal NULL they will not be counted.
COUNT(*) will also perform better than Count(Name). By specifying COUNT(*) you are leaving the optimizer free to use any index it wishes. By specifying COUNT(Name) you are forcing the query engine to use the table, or at least an index that contains the NAME column.
COUNT(name) or COUNT(*) will be somewhat faster because they do not need to return much data. (see Andrew Shepherd's reply on the semantic difference between these two forms of COUNT, as well as COUNT() ). The focus being to "check a name", these differences matter little with the following trick: Instead than count you can also use
SELECT username FROM users where username = 'name' LIMIT 1;
Which will have the effect of checking (the existence) of the name, but returning as soon at one is found.
Count(*) would be faster as MySQL engine is free to choose index to count.
Select statement produces more traffic if there lot of users with same name(lots of rows instead of one).
Try all three and use whichever preforms the best. If they all preform around the same this is an and example of premature optimization and you should probably just use whichever one you feel most comfortable with and tweak it later if necessary. If you superstitious you could also consider using count(1) which I have been told could performance advantages as well.
I would say you should use a select top 1, but your checking a username which is probably an indexed unique column so theoretically count should preform just as well considering there can only be one.
