Query sa mnogo subquerya - problem

Query sa mnogo subquerya - problem 19.07.2008. u 20:31 · pre 222 meseca #1

Dakle, imam problem sa dugačkim upitom koji u sebi ima dosta podupita. Verzija MYSQL-a je 4.11-standard Ne javlja nikakvu grešku nego se samo ne izvede do kraja (dio je php skripte i stranici jednostavno prođe rok za izvršenje). Pokušao sam vaditi upit po upit i sve radi a najzanimljivije je da ako maknem podupit:

"(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' )"

i stavim tri broja (kako bi i trebalo da ispadne) sve radi OK. Ovaj se upit ponavlja par puta i uvijek dolazi nakon: race_result.race_id IN

Mislim da je stvar u optimizaciji i smanjivanju broja upita (a meni to baš i ne ide) pa bi Vas molio da mi pomognete jer mi je ovo strašno bitno i hitno. Evo cjelovitog koda:

Code:

SELECT DISTINCT
        ident,

        prvi_i_drugi_bodovi,

        prvi_i_drugi_cunjevi,

        prva_i_druga_vrata,

        race_competitor.cla,

        race_competitor.vehicle,

        race_competitor.club,

        race_competitor.first_name,

        race_competitor.last_name


FROM
(SELECT ident, total_time, master.race_id,

    ifnull((SELECT MIN((total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000)))
            FROM race_result
                LEFT OUTER JOIN `autoslalom_penalties`
                ON
                autoslalom_penalties.st_number=race_result.ident
                AND
                autoslalom_penalties.race_id=race_result.race_id
            WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
                            (SELECT race.race_id from event
                            left outer join race_grp on event.event_id = race_grp.event_id
                            left outer join race on race.race_group_id = race_grp.race_group_id
                            where event.event_id = " . $event . " AND race.race_type_id = '1' )),
    9999999999) as prvi_bodovi,

    ifnull((SELECT MIN((total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000)))
            FROM race_result
                LEFT OUTER JOIN `autoslalom_penalties`
                ON
                autoslalom_penalties.st_number=race_result.ident
                AND
                autoslalom_penalties.race_id=race_result.race_id
            WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
                            (SELECT race.race_id from event
                            left outer join race_grp on event.event_id = race_grp.event_id
                            left outer join race on race.race_group_id = race_grp.race_group_id
                            where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))>prvi_bodovi),
    9999999999) as drugi_bodovi,

    (SELECT pylons
            FROM race_result
                LEFT OUTER JOIN `autoslalom_penalties`
                ON
                autoslalom_penalties.st_number=race_result.ident
                AND
                autoslalom_penalties.race_id=race_result.race_id
            WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
                            (SELECT race.race_id from event
                            left outer join race_grp on event.event_id = race_grp.event_id
                            left outer join race on race.race_group_id = race_grp.race_group_id
                            where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=prvi_bodovi
    ) as prvi_cunjevi,

    (SELECT pylons
            FROM race_result
                LEFT OUTER JOIN `autoslalom_penalties`
                ON
                autoslalom_penalties.st_number=race_result.ident
                AND
                autoslalom_penalties.race_id=race_result.race_id
            WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
                            (SELECT race.race_id from event
                            left outer join race_grp on event.event_id = race_grp.event_id
                            left outer join race on race.race_group_id = race_grp.race_group_id
                            where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=drugi_bodovi
    ) as drugi_cunjevi,

    (SELECT gates
            FROM race_result
                LEFT OUTER JOIN `autoslalom_penalties`
                ON
                autoslalom_penalties.st_number=race_result.ident
                AND
                autoslalom_penalties.race_id=race_result.race_id
            WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
                            (SELECT race.race_id from event
                            left outer join race_grp on event.event_id = race_grp.event_id
                            left outer join race on race.race_group_id = race_grp.race_group_id
                            where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=prvi_bodovi
    ) as prva_vrata,

    (SELECT gates
            FROM race_result
                LEFT OUTER JOIN `autoslalom_penalties`
                ON
                autoslalom_penalties.st_number=race_result.ident
                AND
                autoslalom_penalties.race_id=race_result.race_id
            WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
                            (SELECT race.race_id from event
                            left outer join race_grp on event.event_id = race_grp.event_id
                            left outer join race on race.race_group_id = race_grp.race_group_id
                            where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=drugi_bodovi
    ) as druga_vrata,

    (SELECT prva_vrata+druga_vrata) as prva_i_druga_vrata,
    (SELECT prvi_bodovi+drugi_bodovi) as prvi_i_drugi_bodovi,
    (SELECT prvi_cunjevi+drugi_cunjevi) as prvi_i_drugi_cunjevi

FROM race_result as master
WHERE master.race_id IN
    (SELECT race.race_id from event
    left outer join race_grp on event.event_id = race_grp.event_id
    left outer join race on race.race_group_id = race_grp.race_group_id
    where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND NOT total_time=0
) as big_master

LEFT OUTER JOIN `race_competitor`
        ON
        race_competitor.st_number=big_master.ident
        AND
        race_competitor.race_id=big_master.race_id

ORDER BY big_master.prvi_i_drugi_bodovi

HVALA UNAPRIJED

Re: Query sa mnogo subquerya - problem 19.07.2008. u 21:04 · pre 222 meseca #2

Lepa "kobasica" :-)

Ako ti se bas zuri, prebaci deo posla na PHP i stvar
zavrsi sa nekoliko jednostavnijih upita.

Nisam neki SQL guru, naci ce se vec neko da ti
pomogne oko konkretnog problema.

Konkretno sta moj savet znaci:
Kad je frka onda gledaj prakticnu stranu
i kako odraditi posao na vreme ne gubeci
isto na "umiranju u lepoti".
Naravno, budi disciplinovan i kada prodje panika,
zavrsi stvar kako si je zamislio - najveci benefit ce biti
novo iskustvo i znanje.

Pozdrav,
Bojan

Re: Query sa mnogo subquerya - problem 20.07.2008. u 10:29 · pre 222 meseca #3

Koristenje "IN" u mySQL je ubiija server, to je ubjedljivo nesto najsporije u citavom mySQL. Zaobidji IN.

Potpis

tu nema kašike....

Forum kao aplikacija na telefonu

Srodne teme