WN - Vad är fel i min SQL?

WN (https://www.wn.se/forum/index.php)

- Serversidans teknologier (https://www.wn.se/forum/forumdisplay.php?f=4)

- - Vad är fel i min SQL? (https://www.wn.se/forum/showthread.php?t=1056642)

secag

2013-01-30 13:56

Vad är fel i min SQL?

Kod:

SELECT threads.*, user.id AS user_id, user.username FROM threads JOIN user ON user.id = threads.uid JOIN posts ON posts.tid = threads.id ORDER BY posts.date, threads.date DESC

Jag har ett enkelt forum där det finns en rad med trådar.
I trådarna finns poster där varje inlägg har trådens ID(tid).

Jag vill sortera trådarna på datum igenom det senaste skriva inlägget. Om inte tråden har någon post så ska den sortera på när tråden skrevs. Såhär har jag tänkt mig:

Kod:

ORDER BY posts.date, threads.date DESC

Att man först tar inläggets datum och sen trådens datum. Eller tänker jag fel?

Jag har 2 tabeller. threads och posts.
Tabbellen user hämtar endast användar ID och användarnamn men det funkar bra. Dock funkar inte sorteringen.

Vet ni något? :)

erikoskar

2013-01-30 15:23

Sorteringen borde väl se ut ungefär såhär:

Kod:

ORDER BY posts.date DESC, threads.date DESC

I annat fall blir posts.date ASC(default).

secag

2013-01-30 15:48

Jag får liksom http://i.imgur.com/DYHwBNj.png

Trådarna upprepas :/

secag

2013-01-30 16:03

EDIT: Ja testade nu att göra en tråd utan något inlägg. Då ser jag inte tråden på trådvyn. Hur fixar jag detta?
Löste det nu:
SELECT threads.*, user.id AS user_id, user.username FROM threads JOIN user ON user.id = threads.uid JOIN posts ON posts.tid = threads.id GROUP BY threads.id ORDER BY MAX(posts.date) DESC, threads.date DESC

Hittade på ett annat forum.
Men kan någon förklara vad GROUP BY används för och vad den gör i detta fall?

secag

2013-01-30 16:23

Provade detta nu:

Provade med denna SELECT threads.*, user.id AS user_id, user.username FROM threads JOIN user ON user.id = threads.uid LEFT JOIN posts ON threads.id = posts.tid GROUP BY threads.id ORDER BY MAX(posts.date) DESC, threads.date DESC.
Då ser jag detta: http://i.imgur.com/aIGcge6.png

Alltså, alla trådar syns nu men tråden som inte har något inlägg läggs längst ner även fast den gjordes efter allting annat.

Westman

2013-01-30 17:10

Inte så konstigt, du har ju MAX(posts.date) DESC före threads.date DESC. Alltså sorterar den på posters datum före trådars datum och du har ju inga poster i den som är längst ned. Eller?

Westman

2013-01-30 17:11

Jag misstänker att du vill sortera på en sammanslagen lista av datum från både poster och trådar, har jag rätt?

secag

2013-01-30 17:29

Aa men jag vill ännu klara mig undan för att blanda ihop poster och trådar i en tabell.

Westman

2013-01-31 08:24

Du behöver nog två kolumner i tabellen threads. En för datumet då tråden skapades och en för senaste ändring (t.ex. postning). Då kan du sortera på senaste ändring, oavsett om tråden har en post eller inte.

Conny Westh

2013-01-31 10:51

Jag la upp lite testdata och längst ner hittar du en möjlig lösnin på ditt problem.

Jag ändrade en del kolumnnamn så det dels blir lättare att följa koden och dels så den stämmer med god programmeringssed "id" är inget bra namn på en kolumn särskilt inte om den förekommer i flera tabeller, så jag har gett varje Primärnyckel unika namn enligt god programmeringssed.

Först skapar vi databasstrukturen:

Kod:

delimiter ;



CREATE TABLE `user` (

  `userid` varchar(45) NOT NULL,

  `username` varchar(45) NOT NULL,

  PRIMARY KEY (`userid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;





CREATE TABLE `threads` (

  `threadid` int(11) NOT NULL AUTO_INCREMENT,

  `threadname` varchar(45) NOT NULL,

  `createddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `closed` bit(1) NOT NULL DEFAULT b'0',

  `creatorid` varchar(45) NOT NULL,

  PRIMARY KEY (`threadid`),

  KEY `fk_creatorid_idx` (`creatorid`),

  CONSTRAINT `fk_creatorid` FOREIGN KEY (`creatorid`) REFERENCES `user` (`userid`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;





CREATE TABLE `posts` (

  `postid` int(11) NOT NULL AUTO_INCREMENT,

  `threadid` int(11) NOT NULL,

  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `deleted` bit(1) NOT NULL DEFAULT b'0',

  `creatorid` varchar(45) NOT NULL,

  `comment` varchar(255) DEFAULT NULL,

  `posttext` varchar(10000) NOT NULL,

  PRIMARY KEY (`postid`),

  KEY `fk_creatorid_idx` (`creatorid`),

  KEY `fk_posts_threadid_idx` (`threadid`),

  CONSTRAINT `fk_posts_creatorid` FOREIGN KEY (`creatorid`) REFERENCES `user` (`userid`) ON DELETE NO ACTION ON UPDATE NO ACTION,

  CONSTRAINT `fk_posts_threadid` FOREIGN KEY (`threadid`) REFERENCES `threads` (`threadid`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

sen slänger vi in lite smart testdata:

Kod:

INSERT INTO `wn`.`user` (`userid`, `username`) VALUES ('anna', 'Anna Anderberg');

INSERT INTO `wn`.`user` (`userid`, `username`) VALUES ('bertil', 'Bertil Bertilsson');

INSERT INTO `wn`.`user` (`userid`, `username`) VALUES ('cecilia', 'Cecilia Cederberg');

INSERT INTO `wn`.`user` (`userid`, `username`) VALUES ('david', 'David Davidsson');



INSERT INTO `wn`.`threads` (`threadid`, `threadname`, `createddate`, `closed`, `creatorid`) VALUES ('1', 'Nu är det vår igen', '2013-01-01 00:00:00', 0, 'anna');

INSERT INTO `wn`.`threads` (`threadid`, `threadname`, `createddate`, `closed`, `creatorid`) VALUES ('2', 'Titta vad jag hittade för kul på internet', '2013-01-02 00:00:00', 0, 'bertil');

INSERT INTO `wn`.`threads` (`threadid`, `threadname`, `createddate`, `closed`, `creatorid`) VALUES ('3', 'Kan man bli bättre än så här?', '2013-01-03 00:00:00', 0, 'bertil');

INSERT INTO `wn`.`threads` (`threadid`, `threadname`, `createddate`, `closed`, `creatorid`) VALUES ('4', 'Är hästar däggdjur?', '2013-01-04 00:00:00', 0, 'anna');

INSERT INTO `wn`.`threads` (`threadid`, `threadname`, `createddate`, `closed`, `creatorid`) VALUES ('5', 'Fullt med nya idéer', '2013-01-05 00:00:00', 0, 'bertil');

INSERT INTO `wn`.`threads` (`threadid`, `threadname`, `createddate`, `closed`, `creatorid`) VALUES ('6', 'Vårt gemensamma mål!', '2013-01-06 00:00:00', 0, 'anna');





INSERT INTO `wn`.`posts` (`threadid`, `created`, `creatorid`, `posttext`) VALUES ('1', '2013-01-04 00:00:00', 'anna', 'Berättar denna text något vettigt?');

INSERT INTO `wn`.`posts` (`threadid`, `created`, `creatorid`, `posttext`) VALUES ('1', '2013-01-05 00:00:00', 'bertil', 'Inte den här heller!');

INSERT INTO `wn`.`posts` (`threadid`, `created`, `creatorid`, `posttext`) VALUES ('3', '2013-01-03 00:00:00', 'david', 'Men nu får du väl ge dig?');

INSERT INTO `wn`.`posts` (`threadid`, `created`, `creatorid`, `posttext`) VALUES ('3', '2013-01-04 00:00:00', 'cecilia', 'Kanske för den här gången då?');

Sists så kör vi en fråga för att få fram alla trådarna sorterade efter senaste postningsdatum om det finns ett sånt annars får det bli trådens skapandedatum:

Kod:

SELECT threadid, MAX(senastedatum) as senastedatum

FROM

(

        SELECT threadid, MAX(createddate) AS senastedatum from wn.threads

        GROUP BY threadid



        UNION 



        SELECT threadid, MAX(created)  AS senastedatum from wn.posts

        GROUP BY threadid

) x

GROUP BY threadid

ORDER BY senastedatum DESC;

Conny Westh

2013-01-31 11:21

Jag tänkte även förklara lite hur jag tänkt när jag löste problemet.

Grundprincipen är att dela upp ett stort problem i sina atomära beståndsdelar. Det var ju från början två olika tabeller och man vill ha ut senaste datum grupperat på threadid i var och en av dessa två tabeller.

För att hämta ut senaste datum från threads-tabellen så behöver man skriva så här:

Kod:

SELECT threadid, MAX(createddate) AS senastedatum from wn.threads

GROUP BY threadid

För att få ut senaste datum från posts-tabellen skriver man så här:

Kod:

SELECT threadid, MAX(created)  AS senastedatum from wn.posts

GROUP BY threadid

Sen vill vi bara ha ut det senaste av dessa sammanslagna tabeller och då slår man bara ihop dessa tabeller med en UNION:

Kod:

SELECT threadid, MAX(createddate) AS senastedatum from wn.threads

GROUP BY threadid



UNION 



SELECT threadid, MAX(created)  AS senastedatum from wn.posts

GROUP BY threadid

Men då får vi fler rader per thread och det vill vi inte ha utan endast ett datum per thread. Då behöver vi göra ytterligare en gruppering. Men Group By funkar bara på EN tabell så vi måste först skapa en enda tabell av denna UNION-statement, vi ger tabellen ett alias som 'x':

Kod:

SELECT *

FROM

(

    SELECT threadid, MAX(createddate) AS senastedatum from wn.threads

    GROUP BY threadid



    UNION 



    SELECT threadid, MAX(created)  AS senastedatum from wn.posts

    GROUP BY threadid

) x

Och sen gör vi bara en gruppering och bakvänd sortering på hela kalaset, så var det fixat, lätt som en plätt:

Kod:

SELECT threadid, MAX(senastedatum) as senastedatum

FROM

(

        SELECT threadid, MAX(createddate) AS senastedatum from wn.threads

        GROUP BY threadid



        UNION 



        SELECT threadid, MAX(created)  AS senastedatum from wn.posts

        GROUP BY threadid

) x

GROUP BY threadid

ORDER BY senastedatum DESC;

Conny Westh

2013-01-31 12:14

Det kan ju vara lite smart att lägga upp en StoredProcedure så det blir enkelt att anropa denna procedure om man använder den på fler ställen:

Kod:

-- --------------------------------------------------------------------------------

-- Routine DDL

-- Note: comments before and after the routine body will not be stored by the server

-- --------------------------------------------------------------------------------

DELIMITER $$



CREATE DEFINER=`root`@`localhost` PROCEDURE `ListOfThreadsByLatestUpdate`()

BEGIN

        SELECT threadid, MAX(senastedatum) as senastedatum

        FROM

        (

                SELECT threadid, MAX(createddate) AS senastedatum from wn.threads

                GROUP BY threadid

                UNION 

                SELECT threadid, MAX(created)  AS senastedatum from wn.posts

                GROUP BY threadid

        ) x

        GROUP BY threadid

        ORDER BY senastedatum DESC;

END

Clarence

2013-01-31 13:30

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20461323)

Kod:

SELECT threadid, MAX(senastedatum) as senastedatum

FROM

(

        SELECT threadid, MAX(createddate) AS senastedatum from wn.threads

        GROUP BY threadid



        UNION 



        SELECT threadid, MAX(created)  AS senastedatum from wn.posts

        GROUP BY threadid

) x

GROUP BY threadid

ORDER BY senastedatum DESC;

Jag skulle akta mig för att använda denna metod. Så fort man får lite trafik eller datamängd så kommer den sega ner rejält. Den använder inga index och skapar en temporär tabell som görs filesort på. Möjligtvis att den kan gå rätt OK med t ex MariaDB, men med vanilla MySQL ska man vara försiktig med sådant.

Westmans idé om att uppdatera threads med senaste aktiva datum är mycket bättre.

Conny Westh

2013-01-31 15:23

Citat:

Ursprungligen postat av Clarence (Inlägg 20461340)

Westmans idé om att uppdatera threads med senaste aktiva datum är mycket bättre.

Den lösningen bryter mot en av relationsdatabasteorins grundregler, nämligen redundansproblmatiken. Redundans skapar kvalitetsproblem på själva datat och ska därför undvikas till varje pris.

Min lösning löser trådskaparens problem, och gör det på ett effektivt, enkelt, lättfattligt, underhållsvänligt och tryggt sätt. Sen får trådskaparen välja själv.

Clarence

2013-01-31 15:41

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20461346)

Denormalisering är den enklaste och i särklass mest effektiva lösningen för att lösa många prestanda-problem som kommer av normaliserad välstrukturerad data. Vill du läsa mer om detta etablerade och välanvända koncept kan du börja här: http://en.wikipedia.org/wiki/Denormalization

Just p g a av att läslasten är så mycket högre än skrivlasten blir det ett väldigt effektivt verktyg. På ett forum kan du räkna med en ratio på iaf 100:1, även om det oftare hamnar närmare 1000:1.

Om du inte vet vad det innebär att läsa resultatet från en EXPLAIN på din query så har du inte en aning om din lösning faktiskt är effektiv efter MySQLs query plan. Om du kör den och läser på om betydelsen av resultatet så kan du lätt inse att din query är väldigt ineffektiv.

Conny Westh

2013-01-31 22:24

Citat:

Ursprungligen postat av Clarence (Inlägg 20461347)

För att det över huvud taget ska vara intressant att diskutera denormalisering eller optimering som strider mot de grundläggande principerna för hur man bygger relationsdatabaser (exempelvis normalisering för unvikande av redundans) så måste man göra en kalkyl på vad den eventuella vinsten kan bli vid en genosnittlig belastning, i jämförelse med den tid det tar att utveckla denna förändring i form av mantid och kostnader. För att en optimering ska vara intressant så måste minst fyra minimikriterier vara uppfyllda (ibland behöver fler kritrier vara uppfyllda):

1 - Garanterad datakvalitet
2 - Det ska verkligen finnas ett upplevt behov dvs det måste föreligga ett kännbart prestandaproblem
3 - Den beräknade prestandavinsten ska vara avsevärd
4 - Kostnaden måste stå i rimlig proportion till den tidsvinst man beräknas göra

Jag ger mig aldrig in på optimering ur prestandasynpunkt om det inte finns ett verkligt behov av det, dvs om det är någon som upplever att systemet tar för lång tid att köra. Annars är det rent slöseri med arbetstid.

Conny Westh

2013-01-31 23:19

Kod:

-- --------------------------------------------------------------------------------

-- Routine DDL

-- Note: comments before and after the routine body will not be stored by the server

-- --------------------------------------------------------------------------------

DELIMITER $$



CREATE DEFINER=`root`@`localhost` PROCEDURE `ThreadsByLatestActiveDate`()

BEGIN

        SELECT threadid, MAX(LatestActiveDate) as LatestActiveDate

        FROM

        (

                SELECT threadid, MAX(createddate) AS LatestActiveDate from wn.threads

                GROUP BY threadid

                UNION 

                SELECT threadid, MAX(created)  AS LatestActiveDate from wn.posts

                GROUP BY threadid

        ) x

        GROUP BY threadid

        ORDER BY LatestActiveDate DESC;

END

Jag döpte om proceduren och fixade lite testkod i PHP om någon vill provköra proceduren i PHP.

Kod:

<?php



$host="localhost";

$port=3306;

$socket="";

$user="root";

$password="mypassword";

$dbname="wn";



$con = new mysqli($host, $user, $password, $dbname, $port, $socket)

        or die ('Could not connect to the database server' . mysqli_connect_error());



//$con->close();



$query = "call `wn`.`ThreadsByLatestActiveDate`";



if ($stmt = $con->prepare($query)) 

{

    $stmt->execute();

    $stmt->bind_result($threadId, $latestActiveDate);

    while ($stmt->fetch()) 

        {

        printf("%s, %s\n", $threadId, $latestActiveDate);

    }

    $stmt->close();

}

?>

tartareandesire

2013-01-31 23:41

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20461371)

Jag ger mig aldrig in på optimering ur prestandasynpunkt om det inte finns ett verkligt behov av det, dvs om det är någon som upplever att systemet tar för lång tid att köra. Annars är det rent slöseri med arbetstid.

Då tänker du helt galet. Många system som myndigheter och andra stora organisationer använt sig utav har kollapsat efter ett antal år just eftersom man inte tänkt efter redan från början. Drift kostar i längden mer än utvecklingstiden vid ett tillfälle och optimering i efterhand är mycket mer tidsödande och komplicerad än om den görs redan från början. I det här exemplet tar det inte heller ens längre tid som du påstår, det gäller bara att veta vad man gör.

Att utveckla resurssnåla system ligger dessutom på allas ansvar. Även inom webb- och systemutveckling bör man vara miljömedveten. Naturligtvis kan man inte fördenskull bygga svårtolkade system som saknar all form av logik men i just det här exemplet så är det bara rent löjligt att vara så pass stelbent bara för att man är rädd att bryta mot en obsolet regel som man hakat upp sig på.

Conny Westh

2013-01-31 23:53

Citat:

Ursprungligen postat av tartareandesire (Inlägg 20461379)

Du får gärna visa på hur koden skulle se ut om du optimerrar enligt dina principer.

Jag tycker dock inte att normalisering av relationsdatabaser är obsolete.

Jag har fokuserat på att göra koden enkel och lätt att förstå, så just underhållskostnaden ska bli så låg som möjligt.

Det är fritt fram för den som önskar att visa på de prestandavinster som uppnås vid en optimering ur prestandasynpunkt. Det vore i högsta grad intressant att se.

Jag har bidragit med grundstommen till detta, så någon annan får gärna bidra med det denne anser vara relevant. Gärna med prestandamätningar. M a o bevisa gärna att jag har helt fel.... Då lär jag mig något nytt så dagen är inte förlorad för det...

jonny

2013-02-01 06:16

Jag förstår inte hur det kan finnas trådar utan inlägg. Där ligger nog pudelns kärna.

Clarence

2013-02-01 10:23

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20461381)

Det borde vara tämligen uppenbart att resultatet är vad det är. Men för att vidare illustrera det så la jag in 400 000 poster med random data - lite knappt vad WN har.

Resultatet:

Hämta senaste tråden enligt createddate (detta är synonymt ur prestandasynpunkt med ett extra fält såsom Westman klokt föreslog):
11,4ms exekveringstid

Köra din groteska UNION subquery:
1560ms exekveringstid (eller 4000+ ms med kall bufferpool, från ssd)

Fyller du en sajt med sådana queries för att sedan få lite bra reklam i något populärt medie ... då går din sajt ner när den skulle ha nått sin höjdpunkt. Och du kommer inte hinna åtgärda det innan din peak är borta och all din framgång för denna gång förlorad. Bad luck or bad choices?

danjel

2013-02-01 11:24

En mycket bra tråd ur lärosynpunkt.
Generellt kring databasfrågor och normalisering,
man bör även skilja på prestanda och skalbarhet.

Bara för att en funktion X går långsammare än funktion Y på liten datamängd , säg några tusen rader, så är inte Y bättre.
Ett exempel att man kan dela upp en sql i flera olika frågor och t.ex göra sorteringar och sammanslagningar i php istället för sql. Det kan ofta ta lite längre tid när man mäter prestanda, men är mer skalbart och snabbare när datamängd ökar.
Ett sådant angreppsätt kan belasta php/webbserver mer än db server men det är ju betydligt enklare att skala upp webbservern(a).
Dessutom brukar det vara enklare att underhålla och förstå sådan kod, åtminstone för webbutvecklare som inte har DBA kompetens

Alla tider är GMT +2. Klockan är nu 04:27.