Geek Answers Handbook

Manipulate 15+ million records in mysql with php?

I have a user table containing more than 15 million entries, and when I execute the registration function, I want to check if the username exists. I did the indexing for the username column, and when I ran the query, “ select count(uid) from users where username='webdev'"hmmm, its contents when loading a blank screen were finally hanged up. I do this in my localhost with php 5 and mysql 5. So offer me some technique to handle this situation.

Is mongodb a good alternative to handle this process on our local machine?

Thanks, Nithish.

+5
source share
4 answers

If you just want to check if it exists or not, try not to use it count. Simple select username from users where username='webdev' LIMIT 1can be faster.

ALSO change column type to varcharif it is not already. Do not use user type text. This is much slower.

+5
source

This may be a moot point, but to check and see if the username already exists, I would issue the following request (a small modification to the shamittomar request):

SELECT DISTINCT `username` FROM `users` WHERE `username` = 'webdev';

This, by default, returns a single instance of "webdev" in the "username" column; if you add more parameters, however, this may change your results. For example, if you run

SELECT DISTINCT `user_id`, `username` FROM `users` WHERE `username` = 'webdev';

"user_id" "username".

+2

One thing you can do is change the username indexing from index to unique, which will greatly speed up the search, and as shamittomar said, add limit 1to the end, although this will only help if the value already exists.

+2
source

your username is unique, so you must set a limit of 1 in the request, it will be faster

select count(uid) from users where username='webdev' limit 1
0
source

More articles:


All Articles