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 a user id that matches the username.

The usual way:

 $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 effective than that? Thank you very much, welcome

Answer 1

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

SELECT * is almost always a bad idea; it is much better to specify the column names that you want MySQL to return. Of course, you will need to use mysql_fetch_assoc or something similar to actually get the data in a PHP variable, but at least it saves MySQL the overhead of sending all these columns, which you won’t use anyway.

Alternatively, you may need to use the newer mysqli or PDO library. They are also much more efficient than the old mysql library, which is deprecated.

Answer 2

The “normal” way is to only select the columns that you need.

Any column that is part of the selection and not needed will slow down the extraction. Partly because of network traffic and partly because the database engine may apply certain optimizations if only some columns are selected.

And select * usually ignored in production code.

Answer 3

Correct request:

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

This will work if everything is done correctly. Note that $username must be properly escaped using mysql_real_escape_string . I would recommend looking at parameterized queries to reduce the risk of invulnerability injection of SQL injection.

Another thing you can change is to use mysql_result instead of your loop:

 $the_user_id = mysql_result($result, 0); 

This assumes that you know you only get one line.

Answer 4

You not only can, but must. Unfortunately, too many young developers are used to picking too much data. After running the query, you do not need to do a while loop, since you only have one record. Instead, use only the following:

 $sql = sprintf( "SELECT userid FROM users WHERE username = '%s'", $username ); $result = mysql_query( $sql ) or die( mysql_error() ); $data = mysql_result( $result, 0 ); if ( $data ) echo $data; // first userid returned 

The second parameter to mysql_result() is the index of the row you want to get. 0 is the first.

Answer 5

Yes, this is a great optimization practice!

In addition, if you want to take another step and make it super-optimized, try adding LIMIT, for example: $query=mysql_query("SELECT userid FROM users WHERE username='$username' LIMIT 1");

This, of course, assumes that you expect to return only one user ID and stop the database engine when it finds it.

But the most important thing: ALWAYS CHOOSE / SANIT YOUR INPUT DATA ! (can't stress it well enough)

Answer 6

You can do this with a single line of code

 echo array_values(mysqli_fetch_array($mysqli->query("SELECT name FROM user WHERE id='$userid'")))[0]; 

Before using this function, you must connect to the database as shown below.

  $mysqli = new mysqli('HOST', 'USERNAME', 'PASSWORD', 'DATABASE'); 

Credits @samuel t thomas