sezarsaman sezarsaman - 1 month ago 5
MySQL Question

80K rows mysql users table cause login problems

I've a large users table on my database with 80k users which cause mostly login problems.

I have a solution! Don't know whether it's a good one or not!

I want to create other users tables for new users and save last IP of them in it.

So in most cases of login by knowing Ip range I can redirect users to the right table in login. If it's not the right table then it will check other tables.

So I think it will increase speed!

Is it a good solution!? I will be appreciated if anyone come up with a better solution!

Table Structure

Answer

Is it a good solution!?

No, it is not a good solution. It is, in fact, a terrible solution.

From your table definition, we can guess that your users give their email addresses to log in.

Try creating an index on that column.

 ALTER TABLE ci_users ADD INDEX index_email (email);

Once you do that, queries of the form

 SELECT something FROM ci_users WHERE email = 'user-supplied-value'

and

 SELECT something FROM ci_users WHERE email LIKE 'user-supplied-value%'

will start to be very fast, almost as if by magic. That's DBMS technology at work.