SELECT + Count

Tjena!

Håller på att bygga om ett forum jag har och försöker läsa ut alla trådar i ett visst forum + antal svar.. Använder mig av följande kod idag:

SELECT 
ForumMessages.ForumMessagesId,
(SELECT COUNT(*) FROM ForumMessages f1 WHERE f1.TopForumMessagesId = ForumMessages.ForumMessagesId) as ForumMessageChildCount, 
ForumMessages.Header,
ForumMessages.UsersId,
ForumMessages.PublishDate,
ForumMessages.Views,
Users.UserName
FROM ForumMessages,Users
WHERE
ForumCategoriesId = '3'
AND
ForumMessages.UsersId = Users.UsersId
AND
TopForumMessagesId = '0'
ORDER BY
ForumMessages.PublishDate DESC
LIMIT 0, 10

Förklaring: TopForumMessagesId = 0 om det är en tråd, om det är en svar så är TopForumMessagesId = ForumMessagesId...

Men den är väldigt seg, finns det något smartare sätt att göra detta på?

Använder mig av MySQL v4.1.21

mvh
Flow

Har du plockat bort count-en för att vara säker på att det var den som skapade problemet?

SELECT 
T.ForumMessagesId,
COUNT(P.*) as ForumMessageChildCount, 
T.Header,
T.UsersId,
T.PublishDate,
T.Views,
U.UserName
FROM ForumMessages T
JOIN Users U ON T.UsersId = U.UsersId
LEFT JOIN ForumMessages P ON P.TopForumMessagesId = T.ForumMessagesId
WHERE T.ForumCategoriesId = '3' AND T.TopForumMessagesId = '0'
ORDER BY T.ForumMessagesId DESC
LIMIT 0, 10

Vet ej om detta kommer bli mycket fortare eller fortare alls. Men detta är vad jag har gjort i korthet:
Omvandlat subqueryn till en GROUP BY och COUNT. Jag har också gett aliasen kortare namn, P, T och U. (Dessutom tycker jag att du bör ge kolumnerna bättre namn... ForumMessagesId->Id eller PostId, TopForumMessagesId->ParentId eller ParentPostId)
Dessutom ändrade jag sorteringen till id-kolumnen, som rimligen bör ha samma ordning som tiden, men bör gå fortare att sortera. Men vilken du än använder, sätt på indexering för den kolumnen. Sätt alldeles oavsett på indexering för både ForumMessagesId och TopForumMessagesId.

Nu skulle du kunna sortera efter senaste inlägg med ORDER BY MAX(P.ForumMessagesId) DESC men då uppstår ett problem för trådar utan svar... Vad de flesta forummjukvaror brukar göra är att lagra trådar och inlägg i separate tabeller, vilket ger ett antal fördelar.

Tack för ditt svar, fick det dock inte att fungera, kan det bero på mysql version?

Fick detta felmeddelande:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as ForumMessageChildCount,

mvh
flow

I all min trötthet glömde jag GROUP BY.

SELECT 
T.ForumMessagesId,
COUNT(P.*) as ForumMessageChildCount, 
T.Header,
T.UsersId,
T.PublishDate,
T.Views,
U.UserName
FROM ForumMessages T
JOIN Users U ON T.UsersId = U.UsersId
LEFT JOIN ForumMessages P ON P.TopForumMessagesId = T.ForumMessagesId
WHERE T.ForumCategoriesId = '3' AND T.TopForumMessagesId = '0'
GROUP BY T.ForumMessagesId
ORDER BY T.ForumMessagesId DESC
LIMIT 0, 10

Tja!

Får fortfarande samma fel, verkar som om min mysql-server inte tycker om:

COUNT(P.*) as ForumMessageChildCount

?

mvh
Flow

COUNT(P) kanske?

Då blir det unknown column P...?

Blä vad jag dummar mig...

COUNT(P.ForumMessagesId)

Nu funkar det, har prövat den två gånger, men den tar i snitt 330 sek att utföra? Den jag skrev tog 2 sek vad kan detta bero på?

Ok, då var min lösning uppenbarligen sämre. :)
Men, har du satt på indexering för kolumnerna? Detta gör du i phpMyAdmin gå till structure för tabellen klicka på någon av knapparna under action. ForumMessagesId bör du troligen använda som primärnyckel. Detta gör du genom att klicka på nyckelsymbolen. TopForumMessagesId kan du bara indexera normalt, och detta gör du genom att klicka på blixten intill TopForumMessagesId.

Om du kommer åt MySQL via konsolen, eller bara genom mysql_query kan du köra dessa två kommandon istället:

ALTER TABLE ForumMessages ADD PRIMARY KEY (ForumMessagesId);
ALTER TABLE ForumMessages ADD INDEX (TopForumMessagesId);

Låt det sedan stå och tugga 10 minuter eller så.

För övrigt, hur många inlägg finns det i databasen? Har du felsökt vilken del av frågan som tar tid?

Nu funkar det perfekt! 0,3 sek!!!

Jättetack!

mvh
Flow

Det finns för övrigt säkert andra tabeller där du kan indexera kolumner för bättre prestanda. Att sätta UsersId som primärnyckel för Users är t ex något du självklart bör göra.
Dessutom, vilka datatyper har dina id-kolumner?

Skall genast fixa till det, vet att jag har primärnyckel i de flesta tabeller fast det verkade inte vara så i forumet; alla id + views har int som typ och datum osv har datetime...

Tjena igen! Nu kör jag denna på forumet, ett problem är dock att den sorterar så att de senaste huvudinläggen kommer först, jag vill att den skall sortera på de senaste aktiva forumtrådarna.

Den skall alltså sortera på PublishDate, men om det finns svar så skall den sortera på deras PublishDate...