Udvide MySQL query

noget i stil med:

Se det er straks langt sværere! (lavede jeg ikke ovenstående?)

Jeg tror ikke, at det kan gøres uden en subselct - hvis jeg skal være helt ærlig. Der kommer muligvis en (større) sql haj og modbeviser mig, men jeg tvivler.

Så er det under MySQL 4.1, så kan man ikke uden mindst en ekstra query.´

Men jeg skal da gerne lige kigge på det :)

--
Morten Barklund

(godt satset bromer, men nej)

jeg satsede nu ikke rigtigt. Men jeg er enig i barklunds udsagt. Idet vi benytter MAX() over et antal rækker så aner vi ikke hvilket id der hører til rækken med den senste dato og derfor kan vi ikke joine os frem til navnet (så vidt jeg kan se)

barklund> Jeg benytter allerede subselect's andre steder så det er fint nok hvis subselects er det der skal til.
Dog har jeg siddet og prøvet og prøvet men kan ikke få det til at funke, så jeg håber du kan knække den :)
bromer> satsede du bare på at den næste i rækken ville komme med det rigtige svar ;)

safl: hehe.. det kunne det godt ligne.. men jeg havde faktisk et løsnings forslag.. men jeg fik lavet nogle mærkelige taste-kombinationer så jeg fik svaret uden at have min løsning med. Derefter fandt jeg så ud af at den ikke virkede :(

SELECT f.id, f.subject, u.fullname, IFNULL( MAX( c.date ) , f.date ) AS date,
(SELECT users.fullname FROM users INNER JOIN forum ON (forum.user_id = users.user_id) WHERE forum.id = c.id) ORDER BY forum.date DESC LIMIT 1,
IF (
c.id IS NULL , 0, COUNT( 1 )
) AS answer
FROM forum AS f
INNER JOIN users AS u ON f.user_id = u.user_id
LEFT JOIN forum AS c ON f.id = c.parent_id
WHERE f.parent_id =0
GROUP BY f.id
ORDER BY date DESC

måske.. jeg kan ikke lige overskue hvorvidt den kan finde ud af hvad c.id er inde i subselecten :)

Hmm, der var en syntax fejl pga. ) var sat INDEN order by forum.date DESC LIMIT 1. også har jeg tilføjet en AS cfullname, så resultatet er til at fange. Dog er det ikke det korrekte navn den smider ud.

SELECT f.id, f.subject, u.fullname, IFNULL( MAX( c.date ) , f.date ) AS date, (
        SELECT users.fullname
        FROM users
        INNER JOIN forum ON ( forum.user_id = users.user_id )
        WHERE forum.id = c.id
        ORDER BY forum.date DESC
        LIMIT 1
        ) AS cfullname,
        IF (
        c.id IS NULL , 0, COUNT( 1 )
        ) AS answer
        FROM forum AS f
        INNER JOIN users AS u ON f.user_id = u.user_id
        LEFT JOIN forum AS c ON f.id = c.parent_id
        WHERE f.parent_id =0
        GROUP BY f.id
        ORDER BY date DESC

er det konsekvent forkert eller kan det hænge sammen med når c.id er NULL?

Uh, den bliver værre og værre:

SELECT f.id, f.subject, u.fullname, IFNULL(cu.date, f.date) AS date, fu.fullname AS cfullname, IF (c.id IS NULL , 0, COUNT( c.id )) AS answer
        FROM forum AS f
        INNER JOIN users AS u ON f.user_id = u.user_id
        LEFT JOIN forum AS c ON f.id = c.parent_id
        LEFT JOIN forum AS cu ON f.id = cu.parent_id AND cu.id IN (SELECT id FROM forum WHERE parent_id=f.id ORDER BY date DESC LIMIT 1)
        LEFT JOIN users AS fu ON cu.user_id = fu.user_id
        WHERE f.parent_id =0
        GROUP BY f.id
        ORDER BY date DESC

:/

--
Morten Barklund

Det er konsekvent forkert. når c.id er NULL bliver cfullname også null.

Ellers kan bromer's vist bare rettes til f.id istedet for c.id inden i subselect'en.

jeg bliver efterhånden en smule forvirret når jeg ser på den query

safl: Gider du ikke tage barklunds query og paste os resultatet af en explain på den?

Hmm har prøvet med f.id istedet for c.id og det virkede ikke :(
For forum.id = f.id er jo det samme, så der får vi kun den der har oprettet den.

barklund> Jeg får en ubehagelig fejlmeddelse på dit forslag:
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
Jeg kører 4.1.10a så det syntes jeg godt nok er lidt tarveligt.

Jeg er normalt tilhænger af at holde antallet af forespørgsler nede, men jeg tror efterhånden ikke at du får bedre performence ved at holde det i een query.

Hm, vi tager bromer's som udgangspunkt og skriver:

SELECT f.id, f.subject, u.fullname, IFNULL( MAX( c.date ) , f.date ) AS date, (
        SELECT users.fullname
        FROM users
        INNER JOIN forum ON ( forum.user_id = users.user_id )
        WHERE forum.parent_id = f.id
        ORDER BY forum.date DESC
        LIMIT 1
        ) AS cfullname,
        IF (
        c.id IS NULL , 0, COUNT( 1 )
        ) AS answer
        FROM forum AS f
        INNER JOIN users AS u ON f.user_id = u.user_id
        LEFT JOIN forum AS c ON f.id = c.parent_id
        WHERE f.parent_id =0
        GROUP BY f.id
        ORDER BY date DESC

Det er smukt!
Har pillet lidt ved den så den returnere navnet på den der har oprettet tråden i tilfælde af at der ingen svar er.

SELECT f.id, f.subject, u.fullname, IFNULL( MAX( c.date ) , f.date ) AS date, IFNULL((
        SELECT users.fullname
        FROM users
        INNER JOIN forum ON ( forum.user_id = users.user_id )
        WHERE forum.parent_id = f.id
        ORDER BY forum.date DESC
        LIMIT 1
        ),u.fullname ) AS cfullname,
        IF (
        c.id IS NULL , 0, COUNT( 1 )
        ) AS answer
        FROM forum AS f
        INNER JOIN users AS u ON f.user_id = u.user_id
        LEFT JOIN forum AS c ON f.id = c.parent_id
        WHERE f.parent_id =0
        GROUP BY f.id
        ORDER BY date DESC

Tak for hjælpen til jer begge endnu engang.
Hvordan skal pointfordelingen være?

Bare jeg får et point mere end bromer, så må du selv bestemme ;)

dunno.. men her er et svar :)

safl: for guds skyld.. giv os det samme.. så lover jeg at jeg generer barklund de næste 2 uger på ICQ..

safl: Kunne man få dig til at smide output fra en explain?

Ja...

nej.. det gjorde du bare ikke.. nu skal jeg gemme mig fra barklund det næste stykke tid.. suk

hehe så kan i bitche lidt over den ;)

den explain kostede lige et point.

1      PRIMARY      f      ALL      NULL      NULL      NULL      NULL      30      Using where; Using temporary; Using filesort
1     PRIMARY     u     ALL     PRIMARY     NULL     NULL     NULL     2     Using where
1     PRIMARY     c     ALL     NULL     NULL     NULL     NULL     30    
2     DEPENDENT SUBQUERY     forum     ALL     NULL     NULL     NULL     NULL     30     Using where; Using temporary; Using filesort
2     DEPENDENT SUBQUERY     users     ALL     PRIMARY     NULL     NULL     NULL     2     Using where

men tak for point.. og jeg skulle sige det samme fra barklund der er gået iseng :)

Tak for points :)

Og uf, en ubehagelig explain! Du skal ige have smidt nogle indexes på rundt omkring. Using temporary betyder, at mysql smider det over i en temptabel i hukommelsen inden den udvælger/sorterer - det er ikke godt. Men using filesort betyder, at mysql rent faktisk skriver midlertidige tabeller på disken, hvor den sorterer _på_ disken inden den finder sit resultat.

Begge oinhs på user kan du se bruger PRIMARY og kun Using where, så det er fint nok. Men der skal nogle indexes på forum.

Så jeg foreslår indexes på:

ALTER TABLE forum ADD INDEX id_user (id, user_id)
ALTER TABLE forum ADD INDEX parent_id_user (parent_id, user_id, date)
ALTER TABLE forum ADD INDEX parent_id_date (parent_id, date)

Det tror jeg vil hjælpe lidt - selvom jeg nu ikke er helt sikker på syntaksen for de der indexes, så forstår du vil meningen :)

--
Morten Barklund

Hvad skulle der være galt med syntaxen? Den overholder da fint syntaxen for ALTER TABLE: http://dev.mysql.com/doc/mysql/en/alter-table.html

(jeg kunne ikke huske, om man skrev sine indexes sådan, men er det rigtigt, så er det rigtigt)

Det er en mulighed. CREATE INDEX er en anden http://dev.mysql.com/doc/mysql/en/create-index.html