Index aber richtig gesetzt

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • Index aber richtig gesetzt

    Hallo Forum,

    Lange hab ich jetzt versucht selbst eine Lösung für mein "Problemkind" zu finden und zu basteln.

    Ich weiß mittlerweile - eigendlich erst seit heute - dass bei einem mehrspaltigen Index die Spalten in der Reihenfolge stehen sollen, wie es auch in der WHERE Klausel steht.

    Mein Problem ist aber dass ich keinen Sinnvollen Index setzen kann - hier der Grund:

    Ich habe eine Tabelle mit 9 Spalten
    Spalte 1 => id (primaryKey)
    Spalte 2-6 => Chars, Varchars, Int (Je nachdem was in der Spalte eingetragen wird)
    Spalte 7-9 => int, char, char

    Spalte 1 ist eh logisch und wird hier für den Index nicht benötigt.
    Spalte 7-9 sind immer automatisch bei einem Query dabei und ganz wichtig.
    Spalte 2-6 können je nachdem was der User sucht varieren.

    Ein Beispiel:
    .) User sucht in Spalte 2 und 3 - 7-9 kommt aut. wieder dazu
    .) User sucht in Spalte 2,3,4 - 7-9 kommt wieder aut. dazu
    .) User sucht 6 - 7-9 kommt wieder dazu

    Ich könnte jetzt für jede erdenklich Variation einen Index setzen und mir dann spätestens nach 10 Minuten die Kugel geben, weil die Inserts und Updates nie wieder fertig werden - das ist nicht Ziel.

    Bringt es etwas wenn ich einen Index über alle relevanten Spalten lege auch wenn lt. Query dann nur wie im letzten Beispiel 6,7,8,9 benötigt wird.

    Was ich mal probiert habe war - weil es unheimlich schnell ging in einem anderen Bereich der Seite - das ganze mittels FULLTEXT zu lösen - hat mir aber nur einen dezenten Absäufer gebracht weil der ewig lange gebraucht hat bis die Ergebnisse gelistet wurden und ausserdem nicht alle Spalten mit einbezogen werden konnten, da FULLTEXT nur für CHAR, VARCHAR, BLOB geht.

    Wie soll ich hier einen sinnvollen Index aufbauen, ohne mir die INSERT's und UPDATE's zu killen.
    In dieser Tabelle befinden sich grad mal 250.000 Datensätze, die in Bälde die 500.000er Grenze überschreiten werden.
    Also kein Riese - und dauern tut das ganze als wären es 50 Mio. Datensätze.

    Vielen Dank!
    bidgo - Benzin im Blut
    Wir wollen Dich und Deine Karre
    Videoblog & Bilderblog zu verschiedenen Bereichen

  • #2
    Hmm, welche Datenbank verwendest du eigentlich?
    Ich geh einfach mal von MySQL aus...
    Ein mehrspaltiger Index lohnt sich nur dann, wenn die erste Spalte im Index auch immer im WHERE-Teil zwingend vorkommt - sonst wird der Index nicht benutzt.
    Warum kommen die Spalten 7-9 immer automatisch dazu und was sagen sie aus? Wenn die Spalten wirklich so wichtig sind, dann könntest du über diese einen Index machen.
    Eine Beispiel-Query wäre imho ganz nützlich.
    Assembler ist eine Methode,
    Programme, die zu langsam laufen,
    so umzuschreiben,
    dass sie überhaupt nicht mehr laufen.

    Kommentar


    • #3
      Hi,

      ja klar hab ich vergessen die wichtigen Sachen wieder zu erwähnen.
      PHP und Mysql sind meine 2 Lieblinge.

      Es geht um eine KFZ-Plattform
      Die Spalten 7-9 beinhalten:

      7: Status ob eine Anzeige noch aktiv ist oder nicht und ist vom Typ INT
      8: Besagt ob es PKW oder LKW, oder KRAD oder WOMO ist.
      9: Beinhaltet eigendlich nur den Ländercode at oder de oder ch

      Diese 3 Felder sind unerlässlich und kommen wirklich bei jeden SELECT vor.
      Somit wäre hier ein Index über diese 3 Spalten schon mal klug.

      Hier mal ein paar Querys wie sie sein könnten:
      PHP-Code:
      /* Beispiel A */
      SELECT
        id
      herstellermodellweitereFelder
      FROM
        tabelle 
      WHERE
        spalte2
      ='benzin' AND 
        
      aktiv=AND 
        
      fart='PKW' AND 
        
      lcode='at'

      /* Beispiel B */
      SELECT
        id
      herstellermodellweitereFelder
      FROM
        tabelle 
      WHERE
        spalte2
      ='benzin' AND 
        
      spalte4='blau' AND 
        
      aktiv=AND 
        
      fart='PKW' AND 
        
      lcode='at'

      /* Beispiel C */
      SELECT
        id
      herstellermodellweitereFelder
      FROM
        tabelle 
      WHERE
        spalte2
      ='benzin' AND 
        
      spalte3='fiat' AND 
        
      spalte4='blau' AND 
        
      spalte5=25000 AND 
        
      aktiv=AND 
        
      fart='PKW' AND 
        
      lcode='at' 
      Erklärung:
      spalte2 = Treibstoff
      spalte3 = Hersteller
      spalte4 = Farbe
      spalte5 = KM

      aktiv, fart und lcode sind immer mit dabei.

      Und hier würde ich hergehen und einen Index über alle relevanten Spalten legen - würde zumindest logisch klingen.
      Ein mehrspaltiger Index lohnt sich nur dann, wenn die erste Spalte im Index auch immer im WHERE-Teil zwingend vorkommt - sonst wird der Index nicht benutzt.
      Verstehe ich das richtig dass ich dann die WHERE-Klausel anderst rum aufbauen sollte.
      Also zuerst die 3 Spalten die immer kommen und dann erst mit den anderen und alle Spalten im Index so anlegen wie Sie im Query aufgerufen werden.
      Aber das mit der Reihenfolge ist ja das was mir nicht eingeht, da die Reihenfolge nie gleich ist wie man aus den 3 Beispielen ja erkennen kann.
      bidgo - Benzin im Blut
      Wir wollen Dich und Deine Karre
      Videoblog & Bilderblog zu verschiedenen Bereichen

      Kommentar


      • #4
        Les es dir am besten einmal selber durch:
        http://dev.mysql.com/doc/refman/5.1/...n-indexes.html

        Die Reihenfolge ist egal, die erste Spalte muss nur zwingend vorkommen. Anhand den Beispielen auf der verlinkten Seite wird das ja recht deutlich.

        Ich würde es einfach mal ausprobieren und n bissl benchmarken.
        Assembler ist eine Methode,
        Programme, die zu langsam laufen,
        so umzuschreiben,
        dass sie überhaupt nicht mehr laufen.

        Kommentar


        • #5
          Hi,

          ich hab mir das mal durchgelesen und in die Tat umgesetzt.
          Ich habe den Key neu organisiert und jene Spalte die immer mit dabei ist als Erstes angeführt.

          Das Ergebnis ist in der Tat um einiges schneller und Werte unter 1 Sekunde sind keine Seltenheit mehr. Ab und zu dass ich bei der Zeitmessung auf 10-14 Sekunden komme, aber ich glaube das hängt dann mit den Inserts zusammen - also wenn gerade ein großer Insert läuft mit 5000-10000 Datensätzen und ich gerade zu dieser Zeit den SELECT mache.
          Keys temporär deaktivieren beim Insert würde mir hier beim SELECT aber dann das Genick brechen.

          So richtig zufrieden bin ich noch nicht, aber ich habe in der Tat wieder etwas dazugelernt was die Index(e) betrifft.
          Was mir halt auch schleierhaft ist - obwohl der Index bei der Kardinalität die selbe Anzahl hat wie der PrimaryKey ist es schneller.
          Wo es immer noch schwer hackt ist, wenn ich dann für bestimmte Sachen einen COUNT(*) machen muß. Da schwirrt mir sogar die Kiste ab - drotz des Keys.
          bidgo - Benzin im Blut
          Wir wollen Dich und Deine Karre
          Videoblog & Bilderblog zu verschiedenen Bereichen

          Kommentar


          • #6
            Was mir halt auch schleierhaft ist - obwohl der Index bei der Kardinalität die selbe Anzahl hat wie der PrimaryKey ist es schneller.
            Dein Primary Key wird bei deinen Abfragen gar nicht genutzt. Lass dir einfach mal eine deiner Queries erklären ( EXPLAIN SELECT ... ). Übrigens verwendet MySQL stets nur einen Index.

            Wo es immer noch schwer hackt ist, wenn ich dann für bestimmte Sachen einen COUNT(*) machen muß. Da schwirrt mir sogar die Kiste ab - drotz des Keys.
            Beispiel-Query!
            Assembler ist eine Methode,
            Programme, die zu langsam laufen,
            so umzuschreiben,
            dass sie überhaupt nicht mehr laufen.

            Kommentar


            • #7
              EXPLAIN kenn ich, nutz ich immer und immer wieder um eben schlauer zu werden und es besser als zuvor zu machen.

              Heute komm ich nicht mehr dazu Beispiel-Querys zu posten - hole ich aber morgen nach!
              Zur Zeit muß ich mich ein bisserl um den Server kümmern...
              bidgo - Benzin im Blut
              Wir wollen Dich und Deine Karre
              Videoblog & Bilderblog zu verschiedenen Bereichen

              Kommentar


              • #8
                Hi Leute,

                ich weiß, lange her, der Tag hat leider nur 24 Stunden

                Ich hab mich ja wie weiter oben brav an die Vorschläge gehalten.
                Durch die Reorganisation des Querys und des Index, gehts wirklich schneller.

                Bei der folgenden Sache bin ich noch den Query schuldig, den ich gleich posten will, da dieser immer noch meine Bremse ist.

                Zur Erklärung:
                a) ich brauche die Gesamtzahl der Treffer
                b) Ausgabe der Treffer auf 30 pro Seite limitiert.

                Mein Script dazu:
                PHP-Code:
                // Für die Gesamtanzahl der Treffer:
                $SQLcount mysql_query("SELECT count(id) FROM anzeigen WHERE ".$query2.$sorter);
                list(
                $number) = mysql_fetch_row($SQLcount);

                // Die Limitierte Ausgabe:
                $result mysql_query("SELECT * FROM anzeigen WHERE ".$query2.$sorter."LIMIT $left_limit$lines);
                while (
                $row=mysql_fetch_array($result))
                {

                Rufe ich das Script auf, ohne dass ich zuerst alle Treffer suche, gehts unter einer Sekunde.
                Mit Gesamttreffer dauert es zwischen 3 und 10 Sekunden.
                Das versteh ich nicht.

                Klar ist, dass ich beim 2. Query das * noch durch die Spaltennamen ersetzen muß, welche wirklich benötigt werden, damits ein bisserl performanter ist.
                bidgo - Benzin im Blut
                Wir wollen Dich und Deine Karre
                Videoblog & Bilderblog zu verschiedenen Bereichen

                Kommentar


                • #9
                  PHP-Code:
                  $SQLcount mysql_query("SELECT count(id) FROM anzeigen WHERE ".$query2.$sorter); 
                  Der WHERE-Teil besteht aus den Spalten, wo du schon den Index drüber gelegt hast? Zeig am besten mal $query2 und $sorter.
                  Assembler ist eine Methode,
                  Programme, die zu langsam laufen,
                  so umzuschreiben,
                  dass sie überhaupt nicht mehr laufen.

                  Kommentar


                  • #10
                    Hi,

                    Also die Querys könnten folgendermaßen aussehen:
                    PHP-Code:
                    ... WHERE aktiv=AND fart='PKW' AND (Fahrzeugtyp='Van/Kleinbus');
                    ... 
                    WHERE aktiv=AND fart='PKW' AND (Fahrzeugtyp='Van/Kleinbus') AND Kilometer '10000';
                    ... 
                    WHERE aktiv=AND fart='PKW' AND (Fahrzeugtyp='Van/Kleinbus') AND
                    Kilometer '10000' AND (Kraftstoff='Benzin'); 
                    Die sind jetzt ohne dem $sorter.
                    Mit $sorter würde dann noch ein
                    PHP-Code:
                    ... ORDER BY Datum ASC;
                    ... 
                    ORDER BY Erstzulassung ASC;
                    ... 
                    ORDER BY Kilometer DESC 
                    INDEX ist folgenderweise aufgebaut - siehe Screen.
                    Es ist ein Index über mehrer Spalten die in der WHERE-Klausel vorkommen können.
                    Logisch das dadurch auch die Splaten beinhalind welche vom ORDER verlangt werden - sollte dies wichtig sein.
                    Einziger Hacken, die Spalten sind nicht alle vom selben Typ.
                    Bei den CHARS und VARCHARS hab ich zumindes mal gleiche Zeilenlänge eingetragen, da ich hörte dass MYSQ das dann viel lieber hat.
                    Angehängte Dateien
                    Zuletzt geändert von web4free; 26.06.2008, 09:33.
                    bidgo - Benzin im Blut
                    Wir wollen Dich und Deine Karre
                    Videoblog & Bilderblog zu verschiedenen Bereichen

                    Kommentar


                    • #11
                      PHP-Code:
                      $SQLcount mysql_query("SELECT count(id) FROM anzeigen WHERE ".$query2.$sorter); 
                      Dann kannst du dir hier auf jeden Fall das Sortieren sparen. Macht ja überhaupt keinen Sinn. Ich bin mir grad gar nicht sicher(ich glaube es aber), ob MySQL erst sortiert und dann Aggregatsfunktionen macht, aber wenn, dann ist das natürlich ein enormer Performanceverlust.
                      Zuletzt geändert von TheFish511; 25.06.2008, 23:20.
                      Assembler ist eine Methode,
                      Programme, die zu langsam laufen,
                      so umzuschreiben,
                      dass sie überhaupt nicht mehr laufen.

                      Kommentar


                      • #12
                        Hi,

                        Original geschrieben von TheFish511
                        PHP-Code:
                        $SQLcount mysql_query("SELECT count(id) FROM anzeigen WHERE ".$query2.$sorter); 
                        Dann kannst du dir hier auf jeden Fall das Sortieren sparen. Macht ja überhaupt keinen Sinn. Ich bin mir grad gar nicht sicher(ich glaube es aber), ob MySQL erst sortiert und dann Aggregatsfunktionen macht, aber wenn, dann ist das natürlich ein enormer Performanceverlust.
                        Ja klar, das mit dem $sorter beim count(id); ist natürlich Schwachsinn - hätte ich zumindest noch nie gelesen.
                        Den $sorter hab ich vom Count mal weggenommen - liegt aber dennoch zwischen 3 und 10 Sekunden.

                        Leider bringt mir phpMyAdmin keine Zeitangabe wenn ich den count absetze.
                        Wenn ich den normalen Query absetze schreibt mir phpMyadmin eine Zeit von ca. 0,5 - 0,7 Sekunden, was ich glaube immer noch sehr hoch ist.

                        Über EXPLAIN erhalte ich folgende Ausgabe wie in diesem Screen gezeigt.


                        EDIT:
                        Ich hab jetzt mal probiert, die Zeit wie lange das Script braucht herauszukitzeln.

                        count(id) + normaler SELECT => 4,95 - 10 Sekunden (inakzeptabel)
                        normaler SELECT => 2,97 Sekunden
                        count(id) => 6,65 Sekunden

                        Eine zeitlang hab ich versucht mit dem Parameter SQL_CACHE zu arbeiten hab dann aber stellenweise das wieder entfernt, weil sich die Anzahl der relevanten Daten minütlich ändern können.

                        count(id) + normaler SELECT mit SQL_CACHE => 0.00845 Sekunden (Aber Hallo) - leider aber erst nach dem 2. Aufruf und ich glaub auch nur dann, wenn die angeforderten Daten in der DB sich bis zu diesem Zeitpunkt nicht geändert haben, was ja aber wie bereits erwähnt minütlich passieren kann.


                        Angehängte Dateien
                        Zuletzt geändert von web4free; 26.06.2008, 09:32.
                        bidgo - Benzin im Blut
                        Wir wollen Dich und Deine Karre
                        Videoblog & Bilderblog zu verschiedenen Bereichen

                        Kommentar


                        • #13
                          Hallo,

                          also dein Filter-Such-Index dürfte Schwachsinn sein.... Ich habe es zumindest noch nie probiert, wie sich ein Index über 15 Zeilen oder wie viele es auch sind, verhält.

                          so wie es für mich aussieht kommen nur Fahrt und Fahrzeugtyp IMMER bei deiner Suche vor, oder?

                          lege den Index mal nur darüber....

                          Aber bedenke immer: Zu viele Indizes machen die Tabelle auch langsamer!

                          Ich gehe mal davon aus, du nutzt MyISAM. Diese Datenbankengine optimiert besser, wenn man einen COUNT(*) macht als ein COUNT(id). Das liegt einfach daran, dass die id wirklich erst gezählt werden muss. Für ein COUNT(*) hat MyISAM die Informationen schon gecached.
                          signed oder unsigned... das ist hier die Frage

                          Kommentar


                          • #14
                            Hallo Case,

                            Also die Spalten aktiv und fart sind wirklich immer dabei zu 100%.
                            fart heißt wirklich nur fart und nicht fahrt - kommt nicht vom fahren
                            Alle anderen sind dynamisch, weil man ja nicht wissen kann wonach ein User sucht.
                            Klar kann ich über jede Möglichkeit einen Index setzen, aber da bin ich schneller weg vom Fenster als ich Hallo sagen kann, weil ich dann für Importe 3 Tage brauche :-)

                            Nein das mit der Anzahl der Indexe ist mir schon klar.
                            Genutzt wird MyIsam.
                            Nur was ist jetzt bei einen COUNT richtig?
                            Die einen sagen * ist besser, andere wiederum erklären, dass hier die Engine alle Spalten und Rows durchkämmt was Performance frisst.
                            Zuerst hatte ich ja das * und hab dann in 4 Std. alle * durch nur eine Spalte - meistens id weil Primary - ersetzt.
                            Was soll ich jetzt glauben?
                            bidgo - Benzin im Blut
                            Wir wollen Dich und Deine Karre
                            Videoblog & Bilderblog zu verschiedenen Bereichen

                            Kommentar


                            • #15
                              an Gott sollst du glauben

                              ne, also ich glaube eigentlich auch meißt nur so richtig, was ich selber gebenchmarked habe.... also probiere es einfach aus.... genauso solltest du das mit dem Index machen...

                              Und du solltest über spalten, die nicht auf jeden Fall vorkommen auch garkeinen Index setzen... Das hast du wohl vorhin ein wenig falsch verstanden Nur meiner Meinung nach hast du zu große Indizes gesetzt und deshalb wird deine Datenbank ausgebremst.... Versuche mal den Filter-Such-Index zu löschen und dann setzt du einen neuen Index auf fart (und nicht fahrt^^). Dann benchmarkst du, wie lange es mit COUNT(*) und wie lange es mit COUNT(id) dauert. Wenn du davon schonmal das bessere gefunden hast, und dir der query noch zu langsam ist, so kannst du noch ein paar Indizes verändern und kannst dann bei jedem veränderten Index benchmarken, was das jetzt gebracht hat und so kommst du dann zu dem besten Ergebnis.
                              signed oder unsigned... das ist hier die Frage

                              Kommentar

                              Lädt...
                              X