Performance, check if value exists array or query

Question

I need to check if a value exists in my database

I have a table where every user has an unique code. For example: 5h27f.

These values and users add up very quickly. So very soon I might have +2000 unique codes. What's the best, fastest and most efficient way to check if a value is unique?

foreach ($users as $user) {

    $is_unique = FALSE;

    while ($is_unique == FALSE) {
        $code = unique_code();
        $query = "SELECT * FROM unique_code_table WHERE code='$code';";
        $res = $mysqli->query($query);
        if ($res->num_rows > 0 {
        } else {
            $is_unique = TRUE;
        }
    }
}

OR

$query = "SELECT code FROM unique_code_table;";
$res = mysqli->query($query);

$codes = array();
$i = 0;
while ($row = $res->fetch_object()) {
    $codes[$i] = $row->code;
    $i++;
}

$code = unique_code();
while (in_array($code, $codes) {
    $code = unique_code();
}

(this code might not be 100% accurate, I've written this just to explain the purpose of the question.)

@Strawberry because I want to create an unique download link for every user, for every piece of content. For example, I want to send a .pdf file to all my contacts. I'd like to know how many people click etc. And I want a random strings, so they can't 'guess' the code of someone else. — anche
– anche, Commented Mar 15, 2015 at 23:10
Would you be prepared to use a string like this instead? SV50ey57XE6cBy6gDe9q0g — Strawberry
– Strawberry, Commented Mar 15, 2015 at 23:17

Vladimir Ramik · Accepted Answer · 2015-03-15 22:56:08Z

2

I'd say that one query trip to the database vs. potentially 2000+ is significantly better to do. Second script will be significantly faster.

On the first code a LIMIT 1 would do wonders but compared to the second query it will pale as far as benchmarks are concerned.

Put the following at the bottom of your script to fine tune and benchmark: PHP 5.4 +

$sParseTime = microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"];
echo $sParseTime;

answered Mar 15, 2015 at 22:56

Vladimir Ramik

1,9302 gold badges14 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

Performance, check if value exists array or query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related