MySQL - wydajność - komplet danych, (Poza kluczem pola często są NULL) Opcje

[beherit]

Mam pytanie z zakresu wydajnościowego - jak to lepiej napisać, aby każdy wiersz wyniku miał komplet danych i nie robić X podzapytań. Klucz jest nałożony na ts (timestamp) oraz v (identyfikator -> FK)

[SQL] pobierz, plaintext 
 
            SELECT
                    " . $this->_timestamp_db . ",
                    IFNULL(a, (SELECT a FROM log_a_" . self::module_year . " WHERE a IS NOT NULL AND a NOT IN(' ', '1','807','1112','1111','111','11111','111111','22','1234') AND v= lv.v ORDER BY ts DESC LIMIT 1)) a,
                    IFNULL(b, (SELECT b FROM log_a_" . self::module_year . "  WHERE b IS NOT NULL AND v= lv.v AND ts < lv.ts ORDER BY ts DESC LIMIT 1)) b,
                    IFNULL(c, (SELECT c FROM log_a_" . self::module_year . "  WHERE c IS NOT NULL AND v= lv.v AND ts < lv.ts ORDER BY ts DESC LIMIT 1)) c,
                    IFNULL(d, (SELECT d FROM log_a_" . self::module_year . "  WHERE d IS NOT NULL AND v = lv.v AND ts < lv.ts ORDER BY ts DESC LIMIT 1)) d,
                    IFNULL(e, (SELECT e FROM log_a_" . self::module_year . "  WHERE e IS NOT NULL AND v = lv.v AND ts < lv.ts ORDER BY ts DESC LIMIT 1)) e,
                    IFNULL(f, (SELECT f FROM log_a_" . self::module_year . "  WHERE f IS NOT NULL AND v = lv.v AND ts < lv.ts ORDER BY ts DESC LIMIT 1)) f                    
            FROM 
                    log_a_" . self::module_year . " lv
            WHERE
                    v= :v AND 
                    (
                        lv.a IS NOT NULL OR
                        lv.b IS NOT NULL OR
                        lv.c IS NOT NULL OR
                        lv.d IS NOT NULL OR
                        lv.eIS NOT NULL OR
                        lv.f IS NOT NULL
                    ) AND
                    ts >= " . $tstmp['timestamp_ms_last_cron'] . "
            ORDER BY ts ASC ";   
[SQL] pobierz, plaintext 

Dodam, że tabela w tym momencie ma 10MB - maks planowane 100MB pola a,c,d,e,f to CHAR 8, a pole b: ENUM z czterema wartościami.

Ten post edytował beherit 26.04.2012, 07:41:36