Optimierung eines Queries bei 700000 Datensätzen

[Gast]

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.

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`.`userprofile` ON `user`.`userId` = `userprofile`.`userId`
            JOIN `fxp_user`.`searchprofile` ON `userprofile`.`userId` = `searchprofile`.`userId`
            LEFT JOIN `i21s_i18n`.`timezones` ON `user`.`timezone` = `timezones`.`timezone`
            LEFT JOIN `fxp_stats`.`online` ON `searchprofile`.`userId` = `online`.`userId`
            LEFT JOIN `fxp_photoalbum`.`properties` ON `searchprofile`.`userId` = `properties`.`userId`
            WHERE `user`.`profilestatus` = 'complete'
                AND `user`.`country` = 'DE'
                AND `userprofile`.`gender` = 'male'
                AND `searchprofile`.`gender` = 'female'
                AND `userprofile`.`height` BETWEEN 155 AND 185
                AND `userprofile`.`zipcode` IN(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

[jahlives]

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

[ghostgambler]

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

[Gast]

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

[TBT]

schreibe mal EXPLAIN vor die Abfrage im PHPmyadmin, und poste die Ausgabe hier

[Gast]

Hi

(
    [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 where; Using 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

[TBT]

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

[Gast]

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 where; Using 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?

[TBT]

erweitere mal beim user den index "country" um die Spalte "profilstatus"