PHPhulp.nl PHPhulp.nl
35k+ leden Over PHPhulp Offerte opdracht Contact
T

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

Door Tom Joppen op 03-04-2018 18:57 gewijzigd op 03-04-2018 20:42
2.519 views
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
$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'];
?>
Ariën
03-04-2018 20:22
Zou je jouw code tussen code-tags willen plaatsen? Dan is het beter leesbaarder.
B
Ben van Velzen
03-04-2018 20:58
Waarom schrijf je dit niet met JOINS ipv al die losse queries te draaien? Dit ga je anders nooit snel krijgen.
A
Aad B
03-04-2018 21:03
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.

T
Tom Joppen
03-04-2018 21:07 gewijzigd op 04-04-2018 13:28
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!




[size=xsmall]Toevoeging op 03/04/2018 23:32:02:[/size]

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?

<?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]. "%')";
}
?>

[size=xsmall]Toevoeging op 03/04/2018 23:39:07:[/size]

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

Dit maakt dus echt wel een enorm verschil.

[size=xsmall]Toevoeging op 04/04/2018 13:18:42:[/size]


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:

<?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>';
}
}
?>

B
Ben van Velzen
04-04-2018 13:29
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.
T
Tom Joppen
04-04-2018 15:49
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?

<?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)'];

}

?>

[size=xsmall]Toevoeging op 04/04/2018 16:00:34:[/size]

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:

<?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)'];

}

?>

Reageren

Inloggen om te reageren