DB-Suche optimieren


Grunge

Erfahrenes Mitglied
#1
Hallo Leute,

ich bin grad an einer Funktion am basteln, die meine Datenbank nach Usern durchsuchen soll. Problem: Sind in etwa 25.000! Dementsprechend lange dauert jetzt die Suche, so wie ich sie aufgebaut habe. Bin kein Profi, deshalb hoffe ich ihr könnt mir helfen das ganze zu optimieren.

Zum Verständnis:

Der User kann eben nach verschiedenen Suchkriterien suchen. "Größe, Gewicht, Umkreis zum eigenen Wohnort, Alter usw...." (siehe Code) für die Umkreissuche nutze ich die OpengeoDB, was ganz gut funktioniert! Allem in allem funktioniert das so, nur wird dabei die maximale Ausführungszeit durch die php.ini verhindert, auf die ich keinen Zugriff habe!

Hier der Code, in der Hoffnung ihr wisst weiter. Ich danke euch im Voraus:

PHP:
...
case "searchuser":
           
            $return = "<div class=\"people-nearby\">
";

            if($_GET['gender'] == "2"){
                $gender = "usr_gender = '1' OR usr_gender = '0'";
            }else{
                $gender = "usr_gender = '".$_GET['gender']."'";
            }
           
            $umkreis = "SELECT
    dest.zc_zip,
    dest.zc_location_name,
    ACOS(
         SIN(RADIANS(src.zc_lat)) * SIN(RADIANS(dest.zc_lat))
         + COS(RADIANS(src.zc_lat)) * COS(RADIANS(dest.zc_lat))
         * COS(RADIANS(src.zc_lon) - RADIANS(dest.zc_lon))
    ) * 6380 AS distance
FROM zip_coordinates dest
CROSS JOIN zip_coordinates src
WHERE src.zc_zip = '".getPlz($_GET['id'])."'
AND dest.zc_id <> src.zc_id
HAVING distance <= ".$_GET['umkreis']."
ORDER BY distance DESC";
           
            $ur = $db->query($umkreis);
           
            while($u = $ur->fetch_assoc()){

           
                    $sql = "SELECT * FROM sn_users WHERE ".$gender." AND usr_alter >= '".$_GET['alterv']."' AND usr_alter <= '".$_GET['alterb']."' AND usr_plz = '".$u['zc_zip']."' ORDER BY usr_vorname ASC, usr_nachname ASC LIMIT 50";
           
                    $result = $db->query($sql);
                        while($row = $result->fetch_assoc()){
                                if($_GET['gewichtv'] != "" || $_GET['gewichtb'] != "" || $_GET['groessev'] != "" || $_GET['groesseb'] != "" ){
                                    $gewicht = "true";
                                    $groesse = "true";
                                   
                                    $details = "SELECT * FROM sn_users_detais WHERE det_id = '".$row['usr_id']."'";
                                    $dr = $db->query($details);
                                    $det = $dr->fetch_assoc();
                                   
                                    if($_GET['gewichtv'] != ""){
                                        if($_GET['gewichtv'] >= $det['det_gewicht']){
                                            $gewicht = "true";
                                        }else{
                                            $gewicht = "false";
                                        }
                                    }
                                   
                                    if($_GET['gewichtb'] != ""){
                                        if($_GET['gewichtb'] <= $det['det_gewicht']){
                                            $gewicht = "true";
                                        }else{
                                            $gewicht = "false";
                                        }
                                    }
                                   
                                    if($_GET['groessev'] != ""){
                                        if($_GET['groessev'] >= $det['det_groesse']){
                                            $groesse = "true";
                                        }else{
                                            $groesse= "false";
                                        }
                                    }
                                   
                                    if($_GET['groesseb'] != ""){
                                        if($_GET['groesseb'] <= $det['det_groesse']){
                                            $groesse = "true";
                                        }else{
                                            $groesse = "false";
                                        }
                                    }
                                   
                                    if($groesse == "true" && $gewicht == "true"){
                                       
                                        $return .=     "<div class=\"nearby-user\">
                <div class=\"row\">
                  <div class=\"col-md-2 col-sm-2\">
                    <img src=\"".getProfileimg($row['usr_id'])."\" alt=\"user\" class=\"profile-photo-lg\" />
                  </div>
                  <div class=\"col-md-7 col-sm-7\">
                    <h5><a href=\"?section=viewprofile&id=".$row['usr_id']."\">".getName($row['usr_id'])."</a></h5>
                    <p>Software Engineer</p>
                    <p class=\"text-muted\">".round(getDistance($_GET['id'], $row['usr_plz']))." km (".$row['usr_ort'].")</p>
                  </div>
                  <div class=\"col-md-3 col-sm-3\">
                   <a href=\"#\" onclick=\"return addfriend('".$row['usr_id']."','".$_GET['id']."');\" class=\"btn btn-primary pull-right\">Freundschaftsanfrage</a>
                  </div>
                </div>
              </div>";

                                    }
                                }else{
                                    $return .=     "<div class=\"nearby-user\">
                <div class=\"row\">
                  <div class=\"col-md-2 col-sm-2\">
                    <img src=\"".getProfileimg($row['usr_id'])."\" alt=\"user\" class=\"profile-photo-lg\" />
                  </div>
                  <div class=\"col-md-7 col-sm-7\">
                    <h5><a href=\"?section=viewprofile&id=".$row['usr_id']."\">".getName($row['usr_id'])."</a></h5>
                    <p>Software Engineer</p>
                    <p class=\"text-muted\">".round(getDistance($_GET['id'], $row['usr_plz']))." km (".$row['usr_ort'].")</p>
                  </div>
                  <div class=\"col-md-3 col-sm-3\">
                   <a href=\"#\" onclick=\"return addfriend('".$row['usr_id']."','".$_GET['id']."');\" class=\"btn btn-primary pull-right\">Freundschaftsanfrage</a>
                  </div>
                </div>
              </div>";
                                }
            }
            }
           
           
            echo $return."</div>";
            break;
...
der Aufruf erfolgt über folgende JS/JQUERY Funktion:

Javascript:
function search(){
    var gender = document.getElementById('s_gender').value;
    var altervon = document.getElementById('s_agev').value;
    var alterbis = document.getElementById('s_ageb').value;
    var gewichtvon = document.getElementById('s_gewichtv').value;
    var gewichtbis = document.getElementById('s_gewichtb').value;
    var umkreis = document.getElementById('s_umkreis').value;
    var groessebis = document.getElementById('s_groesseb').value;
    var groessevon = document.getElementById('s_groessev').value;
   
    $.get("php/_sql.php?section=searchuser&id="+<?php echo $_SESSION['ID']; ?>+"&gender="+gender+"&alterv="+altervon+"&alterb="+alterbis+"&gewichtv="+gewichtvon+"&gewichtb="+gewichtbis+"&umkreis="+umkreis+"&groesseb="+groessebis+"&groessev="+groessevon, function(e){
        $('#output').html(e);
    })
}
 
Zuletzt bearbeitet von einem Moderator:
#2
Zuerst mal ein kräftiges AUA. Direkt aus $_GET ind das SQL rein, du öffnest deine Türe nicht nur, du hast sie damit für jeden Hacker gleich ganz entfernt!

Zur eigentlichen Frage:
1) Anstelle von <= und >= kennen die meisen DBs den Befehl BETWEEN
2) Warum die Userdeteils seperat holen und nicht mittels eines JOIN and die User anhängen?
3) Die Felder auflisten und nicht mit * einfach alles nehmen
SQL:
SELECT
    u.usr_id,
    d.det_gewicht,
    d.det_groesse
FROM 
    sn_users u, 
    sn_users_detais d
WHERE
    -- Warum heisst die usr_id in der Detailtabelle det_id?
    u.usr_id = d.det_id
    AND {$gender}
    AND u.usr_alter BETWEEN '{$_GET['alterv']}' AND '{$_GET['alterb']}' 
    AND u.usr_plz = '{$u['zc_zip']}' 
ORDER BY 
    u.usr_vorname ASC, 
    u.usr_nachname ASC 
LIMIT 50;
4) deine vielen IF() kürzen
PHP:
//Deine Version
if($_GET['groesseb'] != ""){
    if($_GET['groesseb'] <= $det['det_groesse']){
        $groesse = "true";
    }else{
        $groesse = "false";
    }
}
// Alternative Schreibweise für dasselbe resultat
if($_GET['groesseb'] != "") $groesse = ($_GET['groesseb'] <= $det['det_groesse'] ? 'true' : 'false');
Zum ersten Query kann ich nicht so einfachen sagen, wei man es beschleunigen könnte.
 

Grunge

Erfahrenes Mitglied
#3
Danke! Dein Beitrag hat schonmal geholfen Yaslaw...

Ich habe überlegt, die PLZ In einzelne Tabellen unterzuordnen...

Also alle Tabellen mit 1, 2, 3, 4...
So dass Die Abfragen vielleicht nicht allzugange dauern?
 

Grunge

Erfahrenes Mitglied
#5
Also habe das ganze jetzt nochmal in separate Tabellen gepackt!

und zwar in zip_coordinates_1, 2...und so weiter;)

PHP:
$plzid = substr(getPlz($_GET['id']), 0, 1);

            $umkreis = "SELECT 
    dest.zc_zip, 
    dest.zc_location_name,
    ACOS(
         SIN(RADIANS(src.zc_lat)) * SIN(RADIANS(dest.zc_lat)) 
         + COS(RADIANS(src.zc_lat)) * COS(RADIANS(dest.zc_lat))
         * COS(RADIANS(src.zc_lon) - RADIANS(dest.zc_lon))
    ) * 6380 AS distance
FROM zip_coordinates_{$plzid} dest
CROSS JOIN zip_coordinates_{$plzid} src
WHERE src.zc_zip = '".getPlz($_GET['id'])."'
AND dest.zc_id <> src.zc_id
HAVING distance < ".$_GET['umkreis']."
ORDER BY distance DESC";
funktioniert gar nicht, da bricht mein Browser zusammen :D

Keine Ahnung was da lost ist
 
#8
Ja. Ganz viel. Suche nach "Datenbank indexe" und du bekommst sicher tausende von Links...
Auch dein ANlietungsbuch zur DB sollte ein kapitel dazu haben. Indexe gehören zu den Kernthemen von strukturierten Datenbanken.
 

ComFreek

Mod | @comfreek
Moderator
#10
NIEMALS $_GET direkt in ein Statement schreiben, immer erst prüfen.
Lieber keins von beiden tun, sondern Prepared Statements verwenden. Das ist m. E. die einzig korrekte Lösung.

Und bevor du etwas optimierst, solltest du immer darüber im Klaren sein, welcher Part denn genau langsam ist. Das gilt nicht nur für normalen Code (Profiler), sondern auch für SQL: Jedes DBMS bietet da eine Funktion an, um dir den Ausführungsplan eines Query zu zerpflücken.
Such einfach mal nach "MySQL show execution plan", "MySQL profile query".
 
#11
Und wenn Du schon beim tiefen optimieren bist, hier noch ein paar Ideen:
a) Ich vermute, der CROSS JOIN bei Dir ist überflüssig, Du könntest auch mit einem LEFT OUTER JOIN arbeiten, da Du nur die zip_coordinates einbinden willst, die auch verwendet werden. Genau kann ich das aber nicht sagen, da ich die Tabellen nicht kenne. Intern werden somit weniger Daten angefasst.
https://glossar.hs-augsburg.de/Verbundoperatoren_(Join)

b) Passen alle Datentypen in der DB oder kannst Du ggf. welche verwenden, die weniger Platz verbrauchen, z.B. Tinyint statt int?
https://www.homeconstructor.net/de/mysql-datentypen

c) ComFreek sprach schon den Execution plan an. Damit hängt dann letzendlich auch der Plancache zusammen. Du könnest überlegen, Deine Abfrage als Prepared-Statement (SQL-seitig, nicht php-seitig) zu erstellen. Der Vorteil ist, das Deine Abfrage dann nur einmal im Plancache vorhanden ist und nicht jedesmal als Ad-hoc-Abfrage behandelt wird und somit den Cache vollmüllt. Somit steigt auch die Performance.

d)
SQL Statements werden serverintern in folgender Reihenfolge abgearbeitet:
1) FROM
2) WHERE
3) GROUP BY
4) HAVING
5) SELECT
6) ORDER BY
Daraus ergibt sich: Je weiter ich die "WHERE"-Klausel einschränken kann, desto weniger Datensätze müssen in den nächsten Schritten verarbeitet werden, desto performanter ist die Abfrage. Vielleicht kannst Du Deine Abfrage noch umbauen.
 

Grunge

Erfahrenes Mitglied
#12
EDIT:

Habe jetzt mal folgendes benutzt um die Umkreissuche zu optimieren:
https://www.vektorkneter.de/postleitzahl-umkreissuche-mit-php-und-opengeodb/

Ausführungszeit ist gleich 0. Nun kommt das eigentlich Problem:

Die Abfrage durch 25.000 User dauert lange. Dazu bräuchte jetzt mal nen Tipp vielleicht in Bezug auf diese Index Geschichte? Wäre euch sehr verbunden Zusätzlich kommt dazu, dass er in meinem Fall Nun das ganze natürlich mehrmals durchgeht, für alle In Frage kommenden PLZ


Hey...
Was das $_GET Problem betrifft, das werde ich definitiv ändern! Gut dass ihr mich drauf hingewiesen habt!
also es dauert alles nach wie vor, wobei Yaslaw mit seinem ersten Beitrag schon ne Menge Optimierung geleistet hat. Ich denke das Problem ist tatsächlich die Umkreissuche, auf die ich aber nicht verzichten will....Nur durchsucht er dabei ja nicht nur Alle PLZ auf ihren Umfang, sondern bekommt dann noch gleichzeitig nen Haufen PLZ zurück. Mir ist klar, dass die Ausführung nen Moment dauert....

Wobei es im phpmyadmin relativ schnell geht...

Wie dem auch sei...Selbst wenn ich die Umkreissuche vorerst entferne dauert die Abfrage unendlich lange, so dass vielleicht doch die Abfrage von Yaslaw den RAM frisst...
 
Zuletzt bearbeitet:

ComFreek

Mod | @comfreek
Moderator
#13
b) Passen alle Datentypen in der DB oder kannst Du ggf. welche verwenden, die weniger Platz verbrauchen, z.B. Tinyint statt int?
https://www.homeconstructor.net/de/mysql-datentypen
Kleinerer Datentyp impliziert nicht direkt Performanzgewinn. In ungünstigen Fällen greifst du mit einem kleineren Datentyp nicht entlang Cachezeilen, sondern dazwischen.

Daraus ergibt sich: Je weiter ich die "WHERE"-Klausel einschränken kann, desto weniger Datensätze müssen in den nächsten Schritten verarbeitet werden, desto performanter ist die Abfrage.
An sich korrekt, nur optimieren DBMS Ausführungspläne. Wenn du z. B. in HAVING eine Einschränkung hast, die auch im WHERE funktionieren würde, dann würde jedes gängige DBMS das auch optimieren.
Du hast recht, dass das Aufstellen des Ausführungsplans und das Optimieren Zeit beansprucht! Allerdings ist das Query hier sehr kurz und @Grunge ruft es auch nur einmal vor der while-Schleife auf.

Die Abfrage durch 25.000 User dauert lange. Dazu bräuchte jetzt mal nen Tipp vielleicht in Bezug auf diese Index Geschichte?
Wie gesagt, ohne Profilen ist es nur ein Stochern im Dunkeln im schlimmsten Fall und ein educated guess eines erfahrenen DBMS-Nutzers (nicht ich zumindest ;)) im besten Fall.
Siehe oben:
Und bevor du etwas optimierst, solltest du immer darüber im Klaren sein, welcher Part denn genau langsam ist. Das gilt nicht nur für normalen Code (Profiler), sondern auch für SQL: Jedes DBMS bietet da eine Funktion an, um dir den Ausführungsplan eines Query zu zerpflücken.
Such einfach mal nach "MySQL show execution plan", "MySQL profile query".
 

Grunge

Erfahrenes Mitglied
#14
Danke ComFreek für deinen Post.

Leider steige ich da nach wie vor nicht 100%ig durch.
Wie gesagt ich habe noch nie das Problem gehabt so große Datenbanken zu nutzen.
Kannst du/ihr mir ein explizites Beispiel geben, wie ich das ganze optimieren kann. Bisher sieht das ganze jetzt so aus:
PHP:
$array = ogdbPLZnearby($plz,$umkreis, true);
  
for($i = 0; $i <= count($array); $i++){   
    if(count($array > 1)){
       

        if($i == 0){
                        $p .= "AND (u.usr_plz = '".$array[$i]['zip']."'";

        }else if($i == (count($array))){
                    $p .= ")";
        }else{
                        $p .= " OR u.usr_plz = '".$array[$i]['zip']."'";

        }
   
}
}

$sql = "SELECT
    u.usr_id,
    u.usr_plz,
    u.usr_ort,
    d.det_gewicht,
    d.det_groesse
FROM
    sn_users u,
    sn_users_detais d
WHERE
    u.usr_id = d.det_id
    AND {$gender}
    {$p}
    AND u.usr_alter BETWEEN '{$_GET['alterv']}' AND '{$_GET['alterb']}'
ORDER BY
    u.usr_vorname ASC,
    u.usr_nachname ASC
LIMIT 10";
Das ist mir jetzt schon wieder zuviel $p...Wenn ich jetzt einen Umkreis von, sagen wir mal 100km habe, dementsprechend viele PLZ habe ich, und dem entsprechend länger wird logischerweise die ODER Verknüpfungen von $p.
Selbst wenn ich testweise $p wegnehme dauert die Abfrage immer noch Schweine lang. Es sei denn, ich schränke die Suche natürlich ganz genau ein. Wenn ich aber quasi "alle" User anzeigen lassen will, ist es zu extrem!