[Innocast]

Hej,

Sitter här på morgonkvisten och funderar lite över hur jag ska formulera en SQL-fråga. Vad jag har är en ständigt växande tabell. Jag har en kolumn som är en ENUM( 'true', 'false' ), låt oss kalla den `active`. Default-värdet är 'true'.

Vad jag är ute efter är att endast ha de senaste X raderna satta till true och resten till false. Prövade med följande:

UPDATE `table` SET `active` = 'false' ORDER BY `id` DESC LIMIT X,1000

I mina ögon så sorterar den tabellen bakvänt och tar 1000 rader efter rad X och sätter dom till false. Men det fungerade inte. Prövade även denna sub query variant som inte heller fungerade...

UPDATE `table` SET `active` = 'false' WHERE `active` = 'true' ORDER BY `id` DESC LIMIT ( SELECT COUNT(*) FROM `table` WHERE `active` = 'true') - X

Notera att jag bytt ut X mot ett värde.

Jag skulle ju kunna göra två frågor av det hela, en som räknar ut alla rader och därefter gör en update på det föregående resultatet minus 20. Men det känns inte optimalt.

Scriptet kommer att köras var 5:e minut via crontab och se till att tabellen aldrig blir överfull. Har nämligen en indexerng på `active`och gör väldigt många urval på just rader med `active` = 'true'.

Några tips?

[greenio.se]

Jag tror inte ORDER BY fungerar på en UPDATE över huvud taget faktiskt.

Vad sägs om att ha ett datetime-fält i tabellen och fyll i aktuell tid där när du skriver till tabellen så kan du köra en update WHERE created > $femminutersedan

[Robert]

Lite pseudokod (sql dialekten kan variera):

UPDATE `table` SET `active` = 'false'
UPDATE `table` SET `active` = 'true' WHERE `id` IN (SELECT TOP(10) `id`FROM `table`ORDER BY `id`)

Mao: sätt alla till false, sen plockar du alla x antal id'n sorterade. Ifrån dessa gör du alla active till true.

[Conny Westh]

Frågan är vad du ska åstadkomma egentligen, att sätta ett värde "tillfälligt" som du verkar göra i detta fall är ingen bra lösning för en SQL-fråga.

Du borde i stället välja en fråga där du sorterar i omvänd ordning och sedan tar de översta X raderna, ungefär så här (SQL-server):

SELECT TOP 100 * FROM tabellen ORDER BY createdate DESC

Minns inte hur syntaxen var i MySQL men det går att göra samma sak ....

[Jonas]

Originally posted by ConnyWesth@Jun 26 2009, 11:25
Minns inte hur syntaxen var i MySQL men det går att göra samma sak ....

Kolla svaret ovanför ditt...

[Conny Westh]

Ursprungligen postat av Jonas

Kolla svaret ovanför ditt...

Roberts SQL-sats gör förvisso vad trådskaparen frågade efter, men mitt inlägg var ämnat att ifrågasätta detta och försöka förstå vad trädskaparen egentligen vill ha ut av frågan.

Roberts SQL-sats är mycket ineffektiv eftersom den kräver en "tablescan med sin första SQL-sats där han sätter alla värden till false.

Jag misstänker att ett bättre sätt är att endast köra en fråga som använder ett befintligt index så att de förekomster som efterfrågas visas utan att det sker en fysisk uppdatering av databasen.

Min gissning var att det kanske löser trådskaparens egentliga problem, men det skulle gå hiskeligt mycket snabbare att köra den frågan, exempelvis:

SELECT TOP(10) * FROM `table`ORDER BY `id` DESC )

Alternativt...

SELECT TOP(10) * FROM `table`ORDER BY `createdate` DESC )

[Robert]

ConnyWesth; utan att jag (eller vi) ska spekulera i syftet med trådskaparens sqlfråga så kan man också anta att det booleanska värdet är en form av aggregering, dvs att slippa köra en "order by" om tabellen exempelvis används på andra sätt (kanske i joinar etc). Tabellen kan bli mer logiskt hanterbar om man sätter flaggor på de ställen där vissa vilkor är uppfyllda. Dumt eller ej, som sagt beror det på....

Sen har vi det vanliga problemet med folk som försöker optimera sina updates när de egentligen ska optimera sina selects. Ofta sker det xxx antal selects per x update/insert.

[edit: rättstavning]

[Conny Westh]

Min misstanke är att trådskaparen vill kunna visa de 10 senast registrerade medlemmarna på webbsidan, och då skulle min enkla SELECT vara exakt det som är det han vill åstadkomma.

Att över huvud aget ha en extra kolumn med data med i detta fall reduntant information som enkelt kan fås genom en simpel SELECT är inte helt optimalt.

[Westman]

En stored procedure med while loop fungerar också.