Efficient MySQLi query

Question

I am just looking for clarification on the most efficient way of querying a large SQL database using MySQLi queries.

For a user registration form, I am looking to check the users table in an SQL database (not necessarily called users for security reasons) if the username entered into the form already exists. For large user tables, if efficient queries are not used, this could drag the website speed down.

As a point of note, each user in the table has a unique id.

With using LIMIT 1 in the following MySQLi query, I can stop the query after 1 was found matching.

$username = $_POST['username'];
$database = mysqli_connect($dbServer, $dbUser, $dbPassword, $dbName);
$query = "SELECT `username` FROM `".$table."` WHERE `username` ='".mysqli_real_escape_string($database, $username)."' LIMIT 1";

if (mysqli_query($database, $query)) {
    die ("User exists");
}

I am led to believe that the following is more efficient

$username = $_POST['username'];
$database = mysqli_connect($dbServer, $dbUser, $dbPassword, $dbName);
$query = "SELECT `username` FROM `".$table."` WHERE `username` ='".mysqli_real_escape_string($database, $username)."'";

$result = mysqli_query($database, $query);

if (mysqli_num_rows($result) > 0) {
    die ("User exists");
}

but, I would have thought that the query is still being called to create the $result and with no LIMIT set it would slow things down. Am I correct? Which will be more efficient?

If `username` is indexed this should be quick either way. You shouldn't escape data, parameterize and use prepared statements. What does `explain` show for the 2 execution paths? Is the behavior different between direct MySQL execution and MySQLi? — user3783243, Dec 29 '21 at 14:51
@user3783243 - If you mean that each user has a unique `id` then yes, they do. I am not escaping data within the database, I am escaping data entered into the form by the visitor to prevent SQL injections — Chris Rogers, Dec 29 '21 at 14:53
I mean does `username` have an index on it? You should not be escaping data. — user3783243, Dec 29 '21 at 14:56
I would expect any performance difference between the two to be negligible. But if you want to measure any such difference then you'd need to measure that difference. As an expert guess I *suspect* you'd see more of a performance gain across multiple executions of this from using prepared statements with query parameters instead of this [potentially-sql-injectible](https://stackoverflow.com/q/5741187/328193) string concatenation, as the prepared statement would more easily allow the database engine to cache the execution plan knowing that the only difference is the parameter value. — David, Dec 29 '21 at 15:04

score 0 · Accepted Answer · answered Dec 29 '21 at 15:03

I haven't tried to time the two - in general, efficiency in mySQL is about proper indexing and avoiding putting queries inside loops where it can be avoided, as much of the time is spent interpreting your query. It's almost always a good idea, on simple statements, to put what you want to do in the select statement, and let the mySQL optimization take care of the details. I would use the LIMIT 1 to let it act on the information that you want at most one answer.

Efficient MySQLi query

1 Answers1