0

I'm wondering if it would be better to use a single query and SELECT more data than I might need, or use multiple queries and SELECT only what I need when I need it. For example when the client submits a username and password, my PDO query string is this:

$sql = "SELECT passwordHash FROM users WHERE username=:username";

Then in PHP I verify the submitted password against the hash. If it succeeds, I do more queries to get more data like name and address. But would it be better to add those to my initial password hash query so that I'll already have it in the likely event that the password matches? That way I'd save a query. If the password is wrong though, I'd be wasting that lookup of extra information. In general, would it be better to do less queries and get more data each time, possibly more than necessary, or to do more queries and only get exactly the data I need each time?

  • The answer We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3% is at softwareengineering.stackechange.com – Luuk Feb 3 at 18:27
0

The answer We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3% is at softwareengineering.stackechange.com

|improve this answer|||||
0

In addition to what Luuk says, observe how simple the combined query is:

SELECT name, address
    FROM users
    WHERE username = '...'
      AND passwordHash = '...;

Then if you get a row (the 97% case), you are finished. If you get no row, it is either a bad username or bad pwd. (If you need to distinguish between those two cases, then it gets a little more complex.)

Think of the general question by looking at a Rule of Thumb: "90% of a SELECT is overhead." By that I am referring to network traffic, parsing, developing the query plan, locking, sending results back, etc.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.