0

Short Version

If I have a statement like

SELECT COUNT(id) FROM users WHERE username = 'something' AND password = 'hashed password'

if username column of the row doesn't match 'something' will mysql check password column too or it will check username column of the next row?

More Detail

I'm designing the backend logic for signin system. If mysql check password column (which is a relatively long text) for every user it might put pressure on the database.

If that's the case, I can get hashed password from "WHERE username = 'something' " and do the comparison with backend language but if mysql skips the password column when username is not matched then everything is ok.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Pulse
  • 214
  • 2
  • 9
  • This is called short-circuit evalutation, it was discussed here https://stackoverflow.com/questions/31098611/does-order-matter-in-mysql-for-short-circuiting-of-predicates – EzLo Nov 05 '21 at 07:44
  • *in "WHERE A AND B", will mysql check B if A is false?* In general - yes, if neither A nor B are constants. – Akina Nov 05 '21 at 07:44
  • 1
    On a side note, this is not how password hashing mechanisms normally work. You typically have to fetch the stored hash in order to determine the salt used, and only then you can hash the plain text password supplied by user. If you can obtain the right hash only from the plain password, you're doing something wrong. – Álvaro González Nov 05 '21 at 07:52
  • You don't know the actual execution order. Perhaps the optimizer decides to check B before A. – jarlh Nov 05 '21 at 08:03
  • Basically each node in the AST of the expression in the WHERE clause gets an approximate estimate of the "cost" required to evaluate it. The execution then begins from the node with a lowest "predicted" cost. – IVO GELOV Nov 05 '21 at 08:42
  • ok, thanks everybody! @Álvaro González yeah I think you're right thank you. – Pulse Nov 05 '21 at 08:51
  • ... and not just the hash. You can also have different algorithms and parameters :) – Álvaro González Nov 05 '21 at 09:09
  • 2
    Incidently, the comparison of the password column is the *least* relevant factor that you should think about for the execution speed of your query. The way to limit "pressure on the database" is to add an index on "username". If you have the index, then you will read at most one row, and evaluate the password at most one time, no matter how MySQL decides to execute "A and B". If you don't have an index, MySQL will read the whole table (e.g. users and passwords), and you shouldn't care if MySQL spends a nanosecond doing an additional comparison with "B" or not. – Solarflare Nov 05 '21 at 09:47

0 Answers0