any idea for faster login system?

web_designer_iq

Hi,
I have 300.000 member in my database.
I'm using the following SQL query to check username and password:

SELECT user, pass FROM users WHERE user = '$user' AND pass = '$pass'

but wait.
i have one field of last login user
last_login = > datetime field of user last login

any idea for faster login? by order last_login or create temporary table.

for example: order 1000 last user logined by DESC and check user information.

Please any idea ??

thanks

NogDog

SELECT user, pass, last_login FROM users WHERE user = '$user' AND pass = '$pass' 
ORDER BY last_login DESC LIMIT 1

web_designer_iq

Yes, I know 🙂 thanks
but my question is: that's performance for login ???

NogDog

Guess I don't understand the question/problem. 🙁 If user is the primary key of the table (and thus there cannot be more than one row in the table with a given value for user), then it's pretty cut and dried: you just do your query:

SELECT user, pass, last_login FROM table_name WHERE user='$user' AND pass='$pass'

If there's something else going on, then I need a better description of the table(s) involved and what the functional requirements are.

MarkR

You should probably select the password and compare it in PHP instead, that will make the passwords more secure as they won't be compared according to your database's collation rules.

Moreover, if you have a (ideally unique) index on the "user" column, that query should be fast even if there are a lot of rows in users.

Mark

PS: I hope you deal with any potential SQL injection vulnerabilities.

web_designer_iq

Yes i have user_id (Primary_Key) and i don't have forigen key, i just want to faster check.
note: any command to order users table by last_login DESC (for ever)? becuase i have users they are not logined before 1 year! i want to check users by last_login.
any MySQL command to order users by last login for ever?
thanks

web_designer_iq

hi
see
i have 160922 uesrs that's logined before 366 day.
i don't check it. i want to check last_login users
any idea please? 🙁

MarkR

Have you benchmarked this query and are finding it slow? It should not be, as 300k is not a large table and you have an appropriate index.

If you have an index on the "user" column, it should use that index. This should make the query fast.

MySQL does not do a full-table scan if it can avoid it, so it won't matter what "order" the rows "are in", how fast the query will be, because it will search the user index instead of the table data.

Mark

jazz_snob

can you post sql used to create the table?

A multicolumn index on user and password might help:
alter table tablenamehere add index user_password (user, password);

Or even just an index on user
alter table tablenamehere add index(user);
- or -
alter table tablenamehere add unique index(user);
to force unique user name if you don't already have that constraint in place.

This would help mysql to organize by user name, your primary key is only organizing by id, which doesn't help when it comes time to look up by their name.