DB-Suche optimieren

Grunge

Erfahrenes Mitglied
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:
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.
 
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?
 
Ich habe früher mal mit Oracle gearbeitet, da konnte man Indices anlegen, um die Zugriffe zu beschleunigen. Geht das bei mysql nicht?
 
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
 
Bei diesem SQL kann ich nicht helfen. CROSS JOIN kenne ich nicht.
Aber wie Sempervivum schrieb. Hast du Indexe?
 
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.
 
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".
 
Zurück