Performanceproblem mit DB Abfrage

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

  • Performanceproblem mit DB Abfrage

    Hallo Leute,

    ich habe ein Problem mit einer Abfrage.
    Für die Anzahl der Datensätze dauert mir diese zu lang und ich weis nicht woran es liegen kann.

    Dazu verwende ich folgende Abfrage:

    Code:
    SELECT count( * ) AS total
    
      FROM
    
        products p
    
      INNER JOIN
    
        products_to_categories p2c on p.products_id = p2c.products_id
    
      INNER JOIN
    
        products_description pd on p.products_id = pd.products_id
    
      WHERE pd.language_id = '2'
    Die Abfrage dauert: 0.16045 sek.

    Hier mal ein paar Infos zu den Tabellen und Indizes.

    Tabelle products:

    Anzahl der Datensätze: 40.000
    Anzahl der Spalten: 28
    Primary Key: products_id

    Tabelle products_to_categories:

    Anzahl der Datensätze: 40.000
    Anzahl der Spalten: 2
    Primary Key: products_id, categories_id
    Index: products_id

    Tabelle products_description:

    Anzahl der Datensätze: 80.000
    Anzahl der Spalten: 6
    Primary Key: products_id, language_id
    Index: products_id

    EXPLAIN gibt folgendes aus:

    1 SIMPLE p2c index PRIMARY,e_idx_products_id e_idx_products_id 4 NULL 39922 Using index
    1 SIMPLE p eq_ref PRIMARY,e_idx_mixed PRIMARY 4 oscommerce2.p2c.products_id 1 Using index
    1 SIMPLE pd eq_ref PRIMARY,e_idx_products_id PRIMARY 8 oscommerce2.p2c.products_id,const 1 Using index


    Ich weis nicht mehr weiter und hab auch schon das halbe Internet durchforstet. Vielleicht hat hier jemand eine zündende Idee.

    Vielen Dank und Gruss!

  • #2
    Hallo,

    seh ich das richtig, dass du Mehrfeldprimärschlüssel dort drin hast? Das könnte das Problem sein. Ansonsten sieht's super aus: zwei Equi-Joins, besser geht's nicht.

    Gruß,

    Amica
    [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
    Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
    Super, danke!
    [/COLOR]

    Kommentar


    • #3
      Hi,

      also wenn du meinst das teilweise der PRIMARY Key 2 Werte hat....das stimmt.
      Aber eigentlich macht es doch keinen Unterschied ob ich einen Index mit 2 Werten erstelle oder ob 2 Werte im PRIMARY Key vorhanden sind.

      Die Werte im PRIMARY Key werden ja auch abgefragt bzw. benötigt für die Abfrage.

      Beispiel: Tabelle products_description benötigt für die Abfrage einen Index mit products_description und language_id. Beide Werte sind im PRIMARY Key vorhanden.
      Und soweit ich sehen kann werden alle Indizes von MySQL richtig verwendet.

      Bitte korrigiert mich wenn ich mich Irre.

      Gruss

      Kommentar


      • #4
        Das macht schon einen Unterschied, es sei denn, du vergleichst immer alle schlüsselbildenden Felder. Das tust du aber nicht, also muss MySQL den Index teilen. Probier's einfach mal mit Einfeldprimärschlüsseln aus. Du kannst ja trotzdem einen zweiten Unique Key auf die betreffenden Spalten legen, wenn du willst, aber nur eine Spalte sollte den Primärschlüssel bilden.
        [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
        Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
        Super, danke!
        [/COLOR]

        Kommentar


        • #5
          Nur nochmal um dich nicht falsch zu verstehen:

          Aus dieser Tabelle:

          Code:
          CREATE TABLE IF NOT EXISTS `products_description` (
            `products_id` int(11) NOT NULL AUTO_INCREMENT,
            `language_id` int(11) NOT NULL DEFAULT '1',
            `products_name` varchar(64) NOT NULL,
            `products_description` text,
            `products_url` varchar(255) DEFAULT NULL,
            `products_viewed` int(5) DEFAULT '0',
          
            PRIMARY KEY (`products_id`,`language_id`),
            KEY `products_id` (`products_id`)
          
          ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=40000;
          sollte diese werden:

          Code:
          CREATE TABLE IF NOT EXISTS `products_description` (
            `products_id` int(11) NOT NULL AUTO_INCREMENT,
            `language_id` int(11) NOT NULL DEFAULT '1',
            `products_name` varchar(64) NOT NULL,
            `products_description` text,
            `products_url` varchar(255) DEFAULT NULL,
            `products_viewed` int(5) DEFAULT '0',
          
            PRIMARY KEY (`products_id`),
            KEY `products_id` (`products_id`),
            KEY `products_id+language_id` (`products_id`, `language_id`)
          
          ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=40000;
          Und es macht dann ein Unterschied ob der Optimizer bei der ersten Variante auf PRIMARY zugreift oder in der unteren Variante auf den Index products_id+language_id?

          Gruss und danke für die Mühe!

          Kommentar


          • #6
            Fast ja. Den zweiten Key kannst du dir schenken, der ist identisch zum Primärschlüssel.
            [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
            Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
            Super, danke!
            [/COLOR]

            Kommentar


            • #7
              Also ich habs nun mal getestet.
              Macht leider keinen Unterschied.

              Ich hab keinen Plan mehr warum die Abfrage solange dauert.

              Sonst gibt es ja nicht viel Möglichkeiten an was es liegen kann.

              Noch ne Idee?

              Gruss

              Kommentar


              • #8
                Hast du bei den anderen Tabellen auch alles in Einfeldprimärschlüssel geändert - mindestens dort, wo nur eine Spalte davon benutzt wird?

                Ansonsten wäre es hilfreich zu wissen, ob du lokal auf die Datenbank zugreifst und wie du diese Zeit misst, also ob da auch die Übertragung der Daten mit eingeschlossen ist.
                [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
                Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
                Super, danke!
                [/COLOR]

                Kommentar


                • #9
                  Ich hab die DB mit XAMPP lokal auf meinem Rechner.
                  Die Zeit entnehme ich von phpmyadmin.
                  Gibt es da noch andere Möglichkeiten die Zugriffszeiten rauszufinden?

                  Ich habe alle relevanten Tabellen geändert.

                  Gruss

                  Kommentar


                  • #10
                    Ja, über die Kommandozeile, da ist bei mir der Wert immer kleiner als z. B. in PHPMyAdmin. Arbeitest du an einem Notebook? Wie stark variiert die Zeit bei z. B. 10 Versuchen?
                    [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
                    Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
                    Super, danke!
                    [/COLOR]

                    Kommentar


                    • #11
                      Mit KOmmandozeile kenn ich mich leider nich so aus.
                      Was müsste ich da eingeben?

                      Ich arbeite an einem relativ starken PC mit Windows 7.

                      Die Zeiten variieren um ca. 0.01 - 0.05 sek speziell bei dieser Abfrage.
                      Manchmal hab ich auch utopische Zahlen da stehn, aber ganz selten. Wie z.b. 999.999999 sek.
                      Zuletzt geändert von xtramen; 19.03.2010, 00:06.

                      Kommentar


                      • #12
                        Du öffnest ein Konsolenfenster, gehst in das bin-Verzeichnis von MySQL (nur für den Fall, dass dein PATH nicht gesetzt ist) und schreibst rein:

                        Code:
                        mysql -u[I]USER[/I] -p[I]PASS[/I]
                        use [I]DBNAME[/I]
                        select ... from ... where ...[B];[/B]
                        Edit: Semikolon nicht vergessen
                        Zuletzt geändert von AmicaNoctis; 19.03.2010, 00:40.
                        [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
                        Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
                        Super, danke!
                        [/COLOR]

                        Kommentar


                        • #13
                          Mal eine grundlegende Frage zu Indizes.
                          Man sollte doch für jede abgefragte Spalte einen Index setzen.

                          Wenn möglich nur die PRIMARY Keys vergleichen...wenn das nicht geht auf jedenfall einen Index setzen.

                          So!....jetzt habe ich in einer Abfrage 2 Werte derselben Spalte welche ich vergleiche.

                          z.b.

                          Code:
                          WHERE p.products_id =  p2c.products_id
                          AND p2c.categories_id = cd.categories_id
                          Hier wäre es doch sinnvoll in der Tabelle p2c einen 2 Spaltigen Index zu setzen.
                          Ist es anscheinend nicht. Denn wenn ich das tue braucht die Abfrage 0.25 Sekunden für 20.000 Datensätze LIMIT 10.

                          Das ist eeeewig!

                          Wenn ich allerdings den Index oder PRIMARY nur auf products_id setze dann dauert die Abfrage lediglich 0.0002 Sekunden.

                          Fern mir jeglicher Logik.
                          Sorry für die vielen Fragen aber ich bin grad am Input sammeln.
                          Hab mich zuvor nicht so sehr mit Datenbanken beschäftigt.

                          Aber was meinst du dazu?

                          Kommentar


                          • #14
                            Vergiss das mal mit deinen mehrspaltigen Indizes

                            Die bringen nur was, wenn sie auch jeweils paarweise verglichen werden.

                            Beispiel: Tabelle A hat Spalte A1, A2 und A3. Tabelle B hat Spalte B1, B2 und B3. Wenn eine Abfrage (on-Klausel eines Joins oder Where-Klausel)
                            Code:
                            A1 = B1 and A2 = B2
                            fordert, macht ein Mehrfeldschlüssel über A1 und A2 nur Sinn, wenn er analog auch in Tabelle B über B1 und B2 existiert und am besten die Datentypen auch noch paarweise gleich sind (A1 und B1 vom gleichen Typ und auch A2 und B2).

                            Ein weiterer Anwendungsfall sind mehrspaltige Unique-Indizes, wenn man sicherstellen will, dass z. B. nicht nur der Nachname in der Tabelle eindeutig sein soll, sondern die Kombination aus Nachname und Geburtsdatum eindeutig sein muss, aber ein Nachname mehrfach vorkommen darf, wenn das Geburtsdatum jeweils unterschiedlich ist.

                            In allen anderen Fällen solltest du keine Mehrfeldschlüssel verwenden. Immer wenn MySQL den erst aufspalten muss, weil nur eine Spalte aus dem Mehrfeldschlüssel verglichen wird, verlierst du Performance.
                            [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
                            Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
                            Super, danke!
                            [/COLOR]

                            Kommentar


                            • #15
                              Alles klar.
                              Solangsam kapiere ich das besser mit den indizes.
                              Hab aufgrund dieses Beitrages 10 Tabellen optimieren können.

                              Danke erstmal dafür. Ohne dich wär ich noch tagelang da drann gesessen :-)

                              Wenn ich deine Hilfe noch einmal in Anspruch nehmen dürfte?! :-)
                              Denn bei folgender Abfrage welche ich von vorher 0.2 sek auf nun 0.0005 sek optimieren konnte, besteht noch ein kleines Problem wofür ich keine Erklärung habe.

                              Ich will aus:

                              Code:
                              select count(*) as total
                              
                              from products p
                              
                              inner join products_to_categories p2c on p.products_id = p2c.products_id
                              inner join products_description pd on pd.products_id = p2c.products_id,
                              
                              manufacturers m ,
                              specials s
                              
                              where p.products_status = '1'
                              
                              and p.manufacturers_id = m.manufacturers_id
                              and p.products_id = s.products_id
                              and pd.language_id = '2'
                              and p2c.categories_id = '52'
                              and (p.section_id = '0' || p.section_id = '13')
                              dieses machen:

                              Code:
                              select count(*) as total
                              
                              from products p
                              
                              inner join products_to_categories p2c on p.products_id = p2c.products_id
                              inner join products_description pd on pd.products_id = p2c.products_id,
                              
                              manufacturers m 
                              
                              where p.products_status = '1'
                              
                              and p.manufacturers_id = m.manufacturers_id
                              and pd.language_id = '2'
                              and p2c.categories_id = '52'
                              and (p.section_id = '0' || p.section_id = '13')
                              Wenn ich die Spalte specials nicht mehr in der Abfrage drinn habe dann braucht die Abfrage 0.3 - 0.4 sekunden anstatt 0.0005 sek.

                              Müsste doch eigentlich weniger bzw genauso schnell sein wie vorher.
                              Denn ich frage ja weniger Spalten ab.
                              Das Ergebniss(Datensätze) ist allerdings dasselbe, ist auch gewollt so.

                              Ich komm einfach nich drauf und das fuchst mich so, das glaubst du gar nicht!

                              P.S. was mir spontan auffällt ist das p2c vorher den Primary Key verwendet und nachher NULL. Zwinge ich den Optimizer den primary zu verwenden hilft das aber auch nich weiter.

                              Haste vielleicht noch ne Idee?
                              Zuletzt geändert von xtramen; 19.03.2010, 10:52.

                              Kommentar

                              Lädt...
                              X