1

Lets say I have a table named users with the fields id, name, username, and password. When i write the query for getting all of the fields, is it better to write it this way

$sql = mysqli_query($con,"SELECT * FROM users LIMIT 50");

or this way:

$sql = mysqli_query($con,"SELECT id, name, username, password FROM users LIMIT 50");

for this code:

while($me = mysqli_fetch_array($sql)){ 
$id = $me['id'];
$name = $me['name'];
$user = $me['username'];
$pass = $me['password'];
}

Is one better then the other. Is one more finely tuned then the other. Or are they both the exact same performance wise

user2067005
  • 859
  • 7
  • 15

3 Answers3

2

There is no difference in performance at all. I recommend being explicit about the columns that you want to select in production code, however. If you add a column to the table it could have an effect on the query. It's also clearer to anyone maintaining the code what the query is doing and what values are needed.

In point of fact, using SELECT * is less efficient because MySQL will have to look up the column names and it's less memory efficient because the constructs that PHP creates will have more values than are used (possibly) which costs more memory. However, I think this is negligible enough for my "no difference" comment to stand.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
1

I dont know about performance, but when doing joins this can get you into trouble if you have the same column in more than one table. In this case you need to manually alias which will generally require listing all the columns anyhow. Given that i prefer to remain consitent, and as Explosion Pills mentions its easier to tell whats going on/maintain, than using *.

prodigitalson
  • 60,050
  • 10
  • 100
  • 114
0

Never use * to return all columns in a table–it’s lazy. You should only extract the data you need. Even if you require every field, your tables will inevitably change.

Fore more detail see this

Moyed Ansari
  • 8,436
  • 2
  • 36
  • 57