Can I just SELECT one column in MYSQL instead of all, to make it faster?

Question

I want to do something like this:

$query=mysql_query("SELECT userid FROM users WHERE username='$username'");
$the_user_id= .....

Because all I want is the user ID that corresponds to the username.

The usual way would be that:

$query=mysql_query("SELECT * FROM users WHERE username='$username'");
while ($row = mysql_fetch_assoc($query))
    $the_user_id= $row['userid'];

But is there something more efficient that this? Thanks a lot, regards

Answer 1

Yes, that's a great optimization practice!

Also, if you want to go a step further and make it super-optimized, try adding a LIMIT, like this: $query=mysql_query("SELECT userid FROM users WHERE username='$username' LIMIT 1");

This, of course, assumes you expect only one userid to be returned and will make the database engine stop once it finds it.

But, the most important thing: ALWAYS ESCAPE/SANITIZE YOUR INPUT DATA! (can't stress this well enough)

Answer 2

You've hit the nail right on the head: what you want to do is SELECT userid FROM users WHERE username='$username'. Try it - it'll work :)

SELECT * is almost always a bad idea; it's much better to specify the names of the columns you want MySQL to return. Of course, you'll stil need to use mysql_fetch_assoc or something similar to actually get the data into a PHP variable, but at least this saves MySQL the overhead of sending all these columns you won't be using anyway.

As an aside, you may want to use the newer mysqli library or PDO instead. These are also much more efficient than the old mysql library, which is being deprecated.