I acquired a person table that contains 15+ million records even though doing the registration function if only to check on if the username already exist. Used to do indexing for username column so when i run the query "
select count(uid) from users where username='webdev'" ,. err, its continue loading blank screen finally hanged up. I am carrying this out during my localhost with php 5 &lifier mysql 5. So suggest me some technique additional situation.
Is the fact that mongodb is nice alternative to handle this method within our local machine?
If you want to make sure that it is available or otherwise, avoid while using
count. Only a simple
select username from users where username='webdev' LIMIT 1 might be faster.
ALSO, alter the column type to
varchar, when not already so. Don't user
text type. It's so much reduced.
This can be a moot point, but to check if the user title already is available, I'd problem the next query (a small modification on shamittomar's query):
SELECT DISTINCT `username` FROM `users` WHERE `username` = 'webdev';
This can, automatically, return the only real demonstration of "webdev" within the "username" column should you increase the parameters, though, it might improve your results. A good example being, should you run
SELECT DISTINCT `user_id`, `username` FROM `users` WHERE `username` = 'webdev';
it might return all unique mixtures of "user_id" and "username".
One factor you should do is alter the indexing from the username from
index to unique that can make the search so much faster and like shamittomar stated give a
limit 1 in the finish despite the fact that it'll only help when the value already is available.