0

This is a really broad question, but I have come across it a couple of times in the last few weeks and I was wondering what the general consensus is regarding good practice and efficiency.

1)

SELECT COUNT(*) FROM table WHERE id='$id', name='$name', owner='$owner_id'

and then based on if there is one result then the record matches.

2)

SELECT * FROM table WHERE id='$id'

and then a series of if commands to check the results match.

Now obviously there are advantages to the second solution as it allows for accurate error reports as to the field that does not match... but if that is not required which is more efficient, considered better practice and is there a difference to the load on the mySQL server between the two?

Improve this question

3 Answers 3

Reset to default
2

Option 1 by a long shot. Let SQL do what it is designed to do best, and better than procedural code. That is, filtering and sorting data.

Also, it is a much more efficient use of resources (bandwidth, DB utilization, etc) to pull down only the data you need from the server.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Use 1). Mysql is very efficient in selecting data based on certain conditions.

Improve this answer

Comments

1

Large query can take .1 to 5.1 or more seconds, you need to find, run it and find it. Usually multiple if are way better as PHP is very fast. I did that when I was using it with 5 joins in table with 5 billion products, then I reduce one join and then use if statement to fix it up. Query was taking 4.2 seconds, when I reduced join, it took 3.8s but as you know PHP is way faster.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.