Optimierung eines Queries bei 700000 Datensätzen

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

  • Optimierung eines Queries bei 700000 Datensätzen

    Hallo

    Ich bräuchte mal Hilfe bei der Optimierung eines Queries. Ich möchte eine Suche bauen. Die Suche zieht sich über mehrere Tabellen. In der Datenbank sind jetzt ca 700000 Datensätze.
    Der jetzige Query dauert ca. 5 Sekunden. Das ist aber viel zu langsam. Auf die Felder die in der Where oder ON Klausel sind wurden Indexe gesetzt. Was kann ich noch tun. Oder geht es nicht schneller? Ich habe keinerlei Vergleichsdaten.

    Mein Query sieht so aus.

    PHP-Code:
    SELECT
                    
    `user`.`userId`,
                    `
    user`.`pseudonym`,
                    `
    user`.`lastLogin`,
                    `
    user`.`country`,
                    `
    user`.`language`,
                    `
    userprofile`.`verified`,
                    `
    userprofile`.`dateofbirth`,
                    `
    userprofile`.`zipcode`,
                    CASE 
    WHEN (`online`.`accesstime` + 900) > UNIX_TIMESTAMP(UTC_TIMESTAMP())
                        
    THEN 1
                        
    ELSE 0
                    END 
    as `online`,
                    CASE 
    WHEN `properties`.`pictures` > 0
                        THEN 1
                        
    ELSE 0
                    END 
    as `picture`
                
    FROM `fxp_user`.`user
                
    JOIN `fxp_user`.`userprofileON `user`.`userId` = `userprofile`.`userId`
                
    JOIN `fxp_user`.`searchprofileON `userprofile`.`userId` = `searchprofile`.`userId`
                
    LEFT JOIN `i21s_i18n`.`timezonesON `user`.`timezone` = `timezones`.`timezone`
                
    LEFT JOIN `fxp_stats`.`onlineON `searchprofile`.`userId` = `online`.`userId`
                
    LEFT JOIN `fxp_photoalbum`.`propertiesON `searchprofile`.`userId` = `properties`.`userId`
                
    WHERE `user`.`profilestatus` = 'complete'
                    
    AND `user`.`country` = 'DE'
                    
    AND `userprofile`.`gender` = 'male'
                    
    AND `searchprofile`.`gender` = 'female'
                    
    AND `userprofile`.`heightBETWEEN 155 AND 185
                    
    AND `userprofile`.`zipcodeIN(73560,73540,73563,73574,73572,73571,73575,73579,73527,
    73529,73457,73566,73557,73550,73525,73569,73453,73577,
    73568,73111,73430,73565,73447,73431,73434,89558,73433,
    73553,73113,73460,73486,74429,73491,89551,73072,73547,
    89555,74417,73116,73432,73079,73084,74426,73054,73102,
    73492,73333,73463,73037,73104,73642,73329,73098,89518,
    89520,73479,89547,74405,73667,73035,73655,73312,73494,
    74424,89522,74427,73114,73033,73099,73117,73337,73660,
    73107,73466,73340,74423,73092,89564,89174,73066,73489,
    73108,73326,73105,89542,73614,71566,89197,74586,73635,
    74420,73087,74544,73095,89173,73061,73110,73342,71540,
    73488,73450,74538,89177,74597,73119,89189,73441,73344,
    74541,89428,73650,89191,89537,71549,89447,73669,71554,
    73278,73101,73663,73347,89183,89198,73275,71573,73499)
                    
                    
                    
                    
                    
                
                
    ORDER BY `user`.`lastLogin`
                
    LIMIT 120 
    Mfg Akkie
    Zuletzt geändert von ; 18.08.2006, 16:56.

  • #2
    Das ist aber viel zu schnell
    ...
    Oder geht es nicht schneller?
    Wenn es schon zu schnell ist warum willste es dann noch schneller ? Oder kriegst du bei dieser Geschwindigkeit nicht alle gewünschten Daten zurück ?
    Wenn es alle Daten sind dann würde ich sagen 70'000 Datensätze in 5 Sekunden zu durchsuchen ist sicherlich nicht langsam

    Gruss

    tobi
    Gutes Tutorial | PHP Manual | MySql Manual | PHP FAQ | Apache | Suchfunktion für eigene Seiten

    [color=red]"An error does not become truth by reason of multiplied propagation, nor does truth become error because nobody sees it."[/color]
    Mohandas Karamchand Gandhi (Mahatma Gandhi) (Source)

    Kommentar


    • #3
      Ich denke auch 5 Sekunden sind bei einem solchen Query nicht langsam, die Frage ist nur wie weit das Resourcen verbraucht.
      Wenn der DB-Server in der Zeit einfach alle anderen Abfragen abschmettert, schafft er zwar die 5 Sekunden, aber der Query ist trotzdem lastig.

      Vielleicht mal ein paar Daten redundant abspeichern? D.h. die JOINs versuchen zu reduzieren, indem man die Werte direkt in fxp_user speichert?!

      Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

      bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
      Wie man Fragen richtig stellt

      Kommentar


      • #4
        Hi

        Danke für die schnellen Antworten. Also die 5 Sekunden sind inakzeptabel. Bei erwünschten 150 Anfragen pro Sekunde steht der Server ja nur.

        Ich werde jetzt eine Extra Tabelle erstellen in die ich die Daten redundant speichere. Wenn ich einen on update Trigger auf die Original Tabellen setze sollten die Daten auch immer aktuell sein.

        Mfg Akkie

        Kommentar


        • #5
          schreibe mal EXPLAIN vor die Abfrage im PHPmyadmin, und poste die Ausgabe hier
          TBT

          Die zwei wichtigsten Regeln für eine berufliche Karriere:
          1. Verrate niemals alles was du weißt!


          PHP 2 AllPatrizier II Browsergame

          Kommentar


          • #6
            Hi

            PHP-Code:
            (
                [
            id] => 1
                
            [select_type] => SIMPLE
                
            [table] => online
                
            [type] => system
                
            [possible_keys] => PRIMARY
                
            [key] => 
                [
            key_len] => 
                [
            ref] => 
                [
            rows] => 0
                
            [Extra] => const row not found
            )
            Array
            (
                [
            id] => 1
                
            [select_type] => SIMPLE
                
            [table] => properties
                
            [type] => system
                
            [possible_keys] => PRIMARY
                
            [key] => 
                [
            key_len] => 
                [
            ref] => 
                [
            rows] => 0
                
            [Extra] => const row not found
            )
            Array
            (
                [
            id] => 1
                
            [select_type] => SIMPLE
                
            [table] => user
                
            [type] => ref
                
            [possible_keys] => PRIMARY,profilestatus,country
                
            [key] => country
                
            [key_len] => 1
                
            [ref] => const
                [
            rows] => 223906
                
            [Extra] => Using whereUsing filesort
            )
            Array
            (
                [
            id] => 1
                
            [select_type] => SIMPLE
                
            [table] => searchprofile
                
            [type] => eq_ref
                
            [possible_keys] => PRIMARY
                
            [key] => PRIMARY
                
            [key_len] => 4
                
            [ref] => fxp_user.user.userId
                
            [rows] => 1
                
            [Extra] => Using where
            )
            Array
            (
                [
            id] => 1
                
            [select_type] => SIMPLE
                
            [table] => userprofile
                
            [type] => eq_ref
                
            [possible_keys] => PRIMARY,zipcode,height,gender
                
            [key] => PRIMARY
                
            [key_len] => 4
                
            [ref] => fxp_user.searchprofile.userId
                
            [rows] => 1
                
            [Extra] => Using where
            )
            Array
            (
                [
            id] => 1
                
            [select_type] => SIMPLE
                
            [table] => timezones
                
            [type] => eq_ref
                
            [possible_keys] => PRIMARY
                
            [key] => PRIMARY
                
            [key_len] => 1
                
            [ref] => fxp_user.user.timezone
                
            [rows] => 1
                
            [Extra] => 

            Mfg Akkie

            Kommentar


            • #7
              bitte mal in Tabellenform, so kann das ja keiner lesen

              das was ich bis jetzt erkennen kann:

              du solltest die Indezies auf user, userprofile und searchprofile vergrößern
              TBT

              Die zwei wichtigsten Regeln für eine berufliche Karriere:
              1. Verrate niemals alles was du weißt!


              PHP 2 AllPatrizier II Browsergame

              Kommentar


              • #8
                PHP-Code:
                ID        select_type        table            type        possible_keys                    key            key_len        ref                                rows        Extra
                -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                1        SIMPLE            online            system        PRIMARY                                                                                    0            const row not found
                1        SIMPLE            properties        system        PRIMARY                                                                                    0            
                const row not found
                1        SIMPLE            user            ref            PRIMARY
                ,profilestatus,country    country        1            const                            223906        Using whereUsing filesort
                1        SIMPLE            searchprofile    eq_ref        PRIMARY                            PRIMARY        4            fxp_user
                .user.userId            1            Using where
                1        SIMPLE            userprofile        eq_ref        PRIMARY
                ,zipcode,height,gender    PRIMARY        4            fxp_user.searchprofile.userId    1            Using where
                1        SIMPLE            timezones        eq_ref        PRIMARY                            PRIMARY        1            fxp_user
                .user.timezone            1 
                Was schlägst du für Werte vor?

                Kommentar


                • #9
                  erweitere mal beim user den index "country" um die Spalte "profilstatus"
                  TBT

                  Die zwei wichtigsten Regeln für eine berufliche Karriere:
                  1. Verrate niemals alles was du weißt!


                  PHP 2 AllPatrizier II Browsergame

                  Kommentar

                  Lädt...
                  X