Zoeken in verschillende tabellen (+ 50 000 records) is traag

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Tom Joppen

Tom Joppen

03/04/2018 18:57:38
Quote Anchor link
Ik beheer een simpele interne website (voor opzoekingen van gebruikers / installaties / e.d.). In mijn mysql db zitten ongeveer 20 000 users en 30 000 studenten.
Om users of studenten op te zoeken op naam werkt dit zeer vlot via UNION. Helaas zijn er enkele users die meerdere telefoonnummers en werklocaties hebben (studenten hebben geen telefoonnummer en geen locatie).
De users, studenten, telefoonnummers en locaties steken allemaal in een andere tabel, dit is historisch zo gegroeid (aanpassingen zijn mogelijk).

Voor een opzoeking maak ik gebruik van UNION met enkele NULL waarden op de velden gelijk te houden. Van zodra het een user betreft ga ik, aan de hand van de guid, opzoeken welke telefoonnummers en locaties hij/zij heeft. Deze slaag ik tijdelijk in een variabele die ik na iedere gevonden gebruiker weergeef.
Dit zorgt voor de meeste vertraging (tot 2 seconden voor 10 users).
Dit script wordt aangeroepen via jquery en zodra er 3 letters in het inputfield staan. Bedoeling is ook dat je kan zoeken op locatie en bv. voornaam, of telefoonnummer en naam/locatie.
Hoe kan ik dit het beste optimaliseren? Zelf heb ik slechts een basiskennis van SQL en PHP.

Hieronder kan je een klein voorbeeld terugvinden van de opbouw van de database en het php script dat de opzoekingen doet.


tabel users
guid first_name last_name full_name account_name campus ou
abc a bc a bc abc S employees
def d ef d ef def S employees
ghi g hi g hi ghi M employees


tabel telephone
guid telephone type active
abc +32123 A 1
abc +32456 S 1
def +32789 S 1
def +32123 S 1
ghi +32999 A 0


tabel location
guid location active
abc 123 1
abc 234 1
abc 456 0
def 123 1
ghi 876 1


tabel students
guid first_name last_name full_name account_name campus ou
s001 s 001 s 001 s001 S students
s002 s 002 s 002 s002 M students
s003 s 003 s 003 s003 D students



Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
<?php
$name = $_POST['searchitem'];
$sql_add = '';
$sql_add_loc = '';
$key= explode(" ", $name);
for($i = 0; $i < count($key); $i++){
$sql_add .= "AND (first_name LIKE '%". $key[$i] . "%' OR last_name LIKE '%". $key[$i] . "%' OR full_name LIKE '%". $key[$i]. "%' OR account_name LIKE '%". $key[$i]. "%')";
$sql_add_loc .= "UNION (SELECT null, guid, location, null FROM location WHERE location LIKE '%". $key[$i] ."%')";
$sql_add_loc .= "UNION (SELECT null, guid, null, number FROM telephone WHERE number LIKE '%". $key[$i] ."%')";
}
$sql_emp = "(SELECT ou, guid, null, null FROM users WHERE active <> '2'";
$sql_stu = "(SELECT ou, guid, null, null FROM students WHERE active <> '2'";
$sql_limit = " LIMIT 25 ";
$sql = $sql_emp . $sql_add . ') UNION '. $sql_stu . $sql_add . ') '. $sql_add_loc . $sql_limit;

$result = mysqli_query($db, $sql);
$x = 0;
if (mysqli_num_rows($result) > 0){
    while ($row = mysqli_fetch_assoc($result)){
        $x++;
        $locations = '';
        $nummers = '';
        if ($row['ou'] <> 'students') {
            $zoek_gegevens = "SELECT full_name, first_name, last_name, account_name, ou, campus FROM users WHERE guid='" . $row['guid']. "' LIMIT 1";
            $zg = mysqli_query($db,$zoek_gegevens);
            $gegevens = mysqli_fetch_assoc($zg);
            $zoek_locatie = "SELECT location FROM location WHERE guid='" .$row['guid']."'";
            $result_zoek_locatie = mysqli_query($db, $zoek_locatie);
            if (mysqli_num_rows($result_zoek_locatie) > 0){
                while ($locatie = mysqli_fetch_assoc($result_zoek_locatie)){
                if ($locations == '') $locations = $locatie['location']; else $locations = $locations . '  <br> '. $locatie['location'];
                }
            }
            else {
                $locations = $gegevens['campus'];
            }
            $zoek_telefoon = "SELECT number FROM telephone WHERE guid='" .$row['guid']."'";
            $result_zoek_telefoon = mysqli_query($db, $zoek_telefoon);
            if (mysqli_num_rows($result_zoek_telefoon) > 0){
                while ($nummer = mysqli_fetch_assoc($result_zoek_telefoon)){
                if ($nummers == '') $nummers = $nummer['number']; else $nummers = $nummers . '  </small><br> <small> ' . $nummer['number'];
                }
            }
            else {
                $nummer = 'Unknown';
            }
        } else
        {
            $zoek_student = "SELECT full_name, first_name, last_name, account_name, ou, campus FROM students WHERE guid='" . $row['guid']. "' LIMIT 1";
            $zs = mysqli_query($db,$zoek_student);
            $gegevens = mysqli_fetch_assoc($zs);
            $locations = $gegevens['campus'];
?>
Gewijzigd op 03/04/2018 20:42:56 door Tom Joppen
 
PHP hulp

PHP hulp

12/11/2024 21:21:59
 
- Ariën -
Beheerder

- Ariën -

03/04/2018 20:22:19
Quote Anchor link
Zou je jouw code tussen code-tags willen plaatsen? Dan is het beter leesbaarder.
 
Ben van Velzen

Ben van Velzen

03/04/2018 20:58:57
Quote Anchor link
Waarom schrijf je dit niet met JOINS ipv al die losse queries te draaien? Dit ga je anders nooit snel krijgen.
 
Aad B

Aad B

03/04/2018 21:03:28
Quote Anchor link
Ben van Velzen op 03/04/2018 20:58:57:
Waarom schrijf je dit niet met JOINS ipv al die losse queries te draaien? Dit ga je anders nooit snel krijgen.
en verder ook nog wat indexen op de juiste zoek-attributen dan wordt het wel sneller.
 
Thomas van den Heuvel

Thomas van den Heuvel

03/04/2018 21:06:02
Quote Anchor link
Inderdaad, queries in loops = meestal foute boel.
 
Tom Joppen

Tom Joppen

03/04/2018 21:07:48
Quote Anchor link
Super advies van allen!
Ik was gestart met JOINS maar ben telkens tegen (vooral mijneigen) limitaties aangelopen (bv. slechts 1 locatie of telefoonnummer terug krijgen).
Maar nu ik weet dat dit wel moet werken en dat dit de weg is om op te gaan, ga ik mij hierin verder verdiepen!
Hartelijk dank!




Toevoeging op 03/04/2018 23:32:02:

Ik heb het begin van de code aangepast naar 2x LEFT JOIN maar dit stukje code is nu extreem traag (40 seconden t.o.v. 3 seconden)

Wat doe ik hier fout?

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
<?php
$key= explode(" ", $name);
for($i = 0; $i < count($key); $i++){
$sql = "SELECT full_name, first_name, last_name, account_name, email, ou FROM users u LEFT JOIN telephone t ON u.guid = t.guid LEFT JOIN location l ON u.guid = l.guid
WHERE (u.first_name LIKE '%". $key[$i] . "%'
OR u.last_name LIKE '%". $key[$i] . "%'
OR l.location LIKE '%". $key[$i] . "%'
OR t.number LIKE '%". $key[$i] . "%'
OR u.full_name LIKE '%". $key[$i]. "%'
OR u.account_name LIKE '%". $key[$i]. "%')";
}
?>


Toevoeging op 03/04/2018 23:39:07:

Probleem ontdekt, ik was in de testomgeving vergeten de indexen toe te wijzen.

Dit maakt dus echt wel een enorm verschil.

Toevoeging op 04/04/2018 13:18:42:


Het nadeel dat ik nu heb ondervonden is dat ik zeer veel dubbele records krijg te zien (voor ieder telefoonnummer / locatie ).
Is er een simpele manier om dit te filteren?

Momenteel doe ik het zo:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php
function unique_multidim_array($array, $key) {
    $temp_array = array();
    $i = 0;
    $key_array = array();
    foreach($array as $val) {
        if (!in_array($val[$key], $key_array)) {
            $key_array[$i] = $val[$key];
            $temp_array[$i] = $val;
        }
        $i++;
    }
    return $temp_array;
}

while ($row = mysqli_fetch_assoc($result)){
    $num_array[$row['guid']][] = $row['number'] ;
    $loc_array[$row['guid']][] = $row['location'] ;
    $user_array[] = $row;
}

$details = unique_multidim_array($user_array,'guid');

foreach ($details as $value) {
    echo $value['first_name'];
    $loc_array[$value['guid']] = array_unique($loc_array[$value['guid']]);
        foreach ($loc_array[$value['guid']] as $location) {
            echo $location . '<br>';
        }
    $num_array[$value['guid']] = array_unique($num_array[$value['guid']]);
        foreach ($num_array[$value['guid']] as $tel) {
            echo $tel . '<br>';
        }
}
?>
Gewijzigd op 04/04/2018 13:28:03 door Tom Joppen
 
Ben van Velzen

Ben van Velzen

04/04/2018 13:29:38
Quote Anchor link
Je kan voor dit soort gevallen altijd GROUP_CONCAT of GROUP_CONCAT_WS gebruiken om meerdere telefoonnummers e.d. samen te voegen tot 1 record in de presentatie. Niet vergeten ook een *correcte* GROUP BY toe te passen als je dit doet.
 
Tom Joppen

Tom Joppen

04/04/2018 15:49:55
Quote Anchor link
Super hulp!

GROUP_CONCAT werkt perfect. Het laatste 'probleem' waar ik mee zit, is dat er soms users zijn die 2 kantoren hebben en 1 nummer, of 1 kantoor en 3 nummer.
Indien ze bv. 2 locaties hebben en 1 nummer dan komt er bij de result 2x hetzelfde nummer.
Hoe kan ik dit het meest efficiënts opvangen?

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php

$key= explode(" ", $name);
for($i = 0; $i < count($key); $i++){
    $sql = "SELECT u.guid, full_name, first_name, last_name, account_name, email, GROUP_CONCAT(number), GROUP_CONCAT(location), ou FROM users u
    LEFT JOIN telephone t ON u.guid = t.guid
    LEFT JOIN location l ON u.guid = l.guid
    WHERE (u.first_name LIKE '%". $key[$i] . "%'
    OR u.last_name LIKE '%". $key[$i] . "%'
    OR l.location LIKE '%". $key[$i] . "%'
    OR t.number LIKE '%". $key[$i] . "%'
    OR u.full_name LIKE '%". $key[$i]. "%'
    OR u.account_name LIKE '%". $key[$i]. "%')";
}    
$result = mysqli_query($db, $sql);
while ($row = mysqli_fetch_assoc($result)){
    echo $row['first_name'];
    echo $row['GROUP_CONCAT(location)'];
    echo $row['GROUP_CONCAT(number)'];

}

?>


Toevoeging op 04/04/2018 16:00:34:

DISTINCT had ik al gebruikt maar was deze vergeten bij mijn echo erbij te zetten. Nu werkt alles perfect!

Super bedankt voor alle hulp!

Ter info: mijn huidige code:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php

$key= explode(" ", $name);
for($i = 0; $i < count($key); $i++){
    $sql = "SELECT u.guid, full_name, first_name, last_name, account_name, email, GROUP_CONCAT(DISTINCT number), GROUP_CONCAT(DISTINCT location), ou FROM users u
    LEFT JOIN telephone t ON u.guid = t.guid
    LEFT JOIN location l ON u.guid = l.guid
    WHERE (u.first_name LIKE '%". $key[$i] . "%'
    OR u.last_name LIKE '%". $key[$i] . "%'
    OR l.location LIKE '%". $key[$i] . "%'
    OR t.number LIKE '%". $key[$i] . "%'
    OR u.full_name LIKE '%". $key[$i]. "%'
    OR u.account_name LIKE '%". $key[$i]. "%')";
}    
$result = mysqli_query($db, $sql);
while ($row = mysqli_fetch_assoc($result)){
    echo $row['first_name'];
    echo $row['GROUP_CONCAT(DISTINCT location)'];
    echo $row['GROUP_CONCAT(DISTINCT number)'];

}

?>
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.