CODEWITHSUNDEEP
phpmysql
Sahin Urkin
Sahin Urkin

Reputation: 279

How can i optimise code for update mysql tables?

Well i have this code:

<?php
require_once('../assets/db/DBConnect.php');
$conn = DBConnect::conn();

echo "<pre>";
$usersWithoutSurnames = [];
$usersWithSurnames = [];
$i = 0;

$query = 'SELECT * FROM person where surName IS NULL';
$res = $conn->query($query);
while($row = $res->fetch_assoc()) {
    if ($i > 7)
        break;
    $usersWithoutSurnames[] = $row;
    $i++;
}

foreach ($usersWithoutSurnames as $user) {
    $getSurnameQuery = 'SELECT clientSecondname from policy WHERE clientEGN = ? ORDER BY id DESC LIMIT 1';

    if($stmt = $conn->prepare($getSurnameQuery)) {
        $egn = $user['egn'];
        $stmt->bind_param("s", $egn);

        $r = $stmt->execute();

        $stmt->bind_result($clientSecondname);
        $stmt->fetch();
        $user['surName'] = $clientSecondname;
        $usersWithSurnames[] = $user;
        $stmt->close();
    }
}

var_dump($usersWithoutSurnames);
echo '-------------------------------------------------';
echo "<pre>"; var_dump($usersWithSurnames); exit;

The idea: There is a table with users (person) but some users doesn't have surnames, so i check another table (policy) for their surnames, and fill second array with same users but with surnames, after that i will fill and table with this array. You can see i get only 7 users, cuz when i try to get more users it timeout, can i optimise this code/queries to work with more users?

Tables structure:

person:

|------------------------------------------------
|  Id  | firstName | secondName | lastName | EGN
|   1  | test      |   NULL     |  test    | 123
|   2  | test      |   NULL     |  test    | 321

policy:

|------------------------------------------------------------------------
|  Id  | clientFirstname | clientSecondname | clientLastname | clientEGN
|   1  | test            |   test           |  test          |  123
|   2  | test            |   test           |  test          |  321

Upvotes: 1

Views: 65

Answers (2)

Raptord
Raptord

Reputation: 503

janmyszkier raises a good point, in that it seems like your tables are missing keys and/or indexes. These simple queries should not be crippling your system.

As for optimizing your current SQL, you can combine your two select statements into a single one by joining the two tables:

select 
  person.*,
  policy.clientSecondname
from person
left join policy on policy.clientEGN = person.EGN
where person.secondName is null

Make sure you have indexes on the EGN and clientEGN columns to speed up the join.

Note that your code example refers to a column named "surName", but that column doesn't exist in the table definition your provided. I used secondName instead since it seemed like the best match.

Upvotes: 2

Jan Myszkier
Jan Myszkier

Reputation: 2744

First of all, make sure the tables have indices (if there are none for clientEGN add one with ADD INDEX). this should speed up your search queries "a bit"

once you add the index, please let us now if your code still requires optimizations. The code is not optimal, but easy to understand and maintain. I could suggest doing a JOIN and UPDATE joined with IF within, but that would make the code more complex and problematic for junior dev to maintain, so at this point, I won't advice changing what you currently have.

Upvotes: 1

Related Questions