DB-Suche optimieren

[Fragenfrager]

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]

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...

 

[ComFreek]

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]

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:

$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!