MySQL: Hente ut radnummer etter sortering

[Fryth]

Hei!

Jeg er i gang med et highscore-prosjekt i MySQL og vil gjerne ha med hvilken plass man er på i tillegg til scoren man har fått. Hittil ser det bare slik ut:

 

 

Vel og bra. Jeg har ingen ID-kolonne, men så ikke behovet i en så simpel tabell som dette. Det jeg ønsker er å hente ut hvilken plass alle er på, f.eks "abcdef" er på plass 4 nå, men med en gang noen kommer høyere ligger han på 5. Å renummerere alle under der en ny score kommer virker som en veldig tungvindt løsning.

Problemet med nummerering i selve SQL-spørringen er at hvis jeg henter ut f.eks fra plass nr 3-7, vil nummereringen fortsatt starte på 1..

(Ved bruk av denne:)

SET @row_num = 0;
SELECT @row_num := @row_num + 1 AS 
row_num, username, score FROM highscore
ORDER BY score DESC; 

Noen gode ideer her?

[terjeelde]

Siden du ikke lagrer plassering i databasen, så kommer dette nesten like mye ann på hvordan du bruker den (hvilke språk) som selve databasen.

 

Hvis du f.eks henter ut rad 3-7, så vil du jo kunne vite at første raden du henter ut er plass 3, andre er plass 4, osv?

[Fryth]

Siden du ikke lagrer plassering i databasen, så kommer dette nesten like mye ann på hvordan du bruker den (hvilke språk) som selve databasen.

 

Hvis du f.eks henter ut rad 3-7, så vil du jo kunne vite at første raden du henter ut er plass 3, andre er plass 4, osv?

Beklager at jeg ikke spesifiserte språk. Bruker PHP for å hente ut infoen.

 

3-7 var kun et eksempel. På nåværende tidspunkt kan jeg jo ikke hente ut rad 3-7 med mindre jeg spør etter hele tabellen og deretter plukker ut 3-7 via PHP-kode. Vil ikke dette bli fryktelig tungt å holde på med etterhvert (for PHP-delen)?

 

I SQL har man jo LIMIT osv, litt unødvendig å sende en hel tabell til PHP for så å bruke 5 rader av den, er det ikke?

Endret 14. november 2013 av Fryth

[terjeelde]

Hvis du ikke har mye last, så ville jeg ikke bekymret meg om antall rader var under 10 000 eller så.

 

 

Var kanskje litt upressis i formuleringen min, så la meg gi et eksempel på det jeg tenker på.

 

 

Si f.eks du har en highscore-liste på web, hvor brukere kan bla, med f.eks 100 entries pr. side.

 

Da vil du gjerne bruke LIMIT 100 på første, og vise radene du får tilbake som plass #1, plass #2, etc.

 

Hvis bruke er på side 43 derimot, så vil du gjerne bruke:

 

LIMIT 100 OFFSET 4300

 

Da kan du bare ta utgangspunkt i 4300, og regne derfra.

 

Første raden du får er plass #4301, andre er #4302, etc.

 

Du må jo vite hvilke rader/plasser du vil ha ut når du ber MySQL om dem, og da kan du samtidig bruke det for å sette riktig plass i PHP.

[Fryth]

Da kan du bare ta utgangspunkt i 4300, og regne derfra.

 

Første raden du får er plass #4301, andre er #4302, etc.

Ja, det var slik jeg tolket det etterhvert også.

 

Nå dukker det såklart opp flere ting jeg ikke har tenkt på også - Jeg vil gjerne vise brukeren hvilken plassering han er på, og dermed vet jeg jo ikke verken OFFSET eller plassering ettersom utgangspunktet blir navnet til brukeren.

Så først må jeg finne ut hvilken rad brukeren ligger på når jeg har sortert, og deretter kjøre LIMIT og OFFSET..?

[terjeelde]

Litt av trikset med SQL er å glemme at det er rader, og bare tenke på det som data. Kraften i SQL ligger ikke i tabeller eller rader, en fleksibel spørring av dataene.

 

Du kan gjøre f.eks:

 

SELECT count(id) FROM highscore WHERE score >= myscore;

 

Hvis du ikke har din score akkurat der, så kan du gjøre noe sånt som:

 

SELECT count(id) FROM highscore

WHERE score >= ( SELECT score FROM highscore WHERE username = myname );

 

 

Så får du antall brukere som har en høyere highscore, og dermed din plassering (hvis du regner to brukere med samme score som å ha en plass i listen hver, istedet for å ha en på deling).

 

Det vil ikke nødvendigvis være en supereffektiv måte å gjøre det på, men for inntil noen tuser brukere spiller det neppe så stor rolle.

 

(Jeg har ikke bruke MySQL på en del år, så kan ikke garantere 100% for syntaxen).

[quantum]

Hvis du ikke har en id-kolonne med teknisk nøkkel bruker du username-kolonnen og har definert pk på denne håper jeg?

 

Jeg prøver meg med denne:

select
username,
score,
(select count(username) from highscore hs2 where hs2.score >= hs1.score and hs1.username <> hs2.username) + 1 as rank
from highscore hs1
where hs1.username = 'albert'

Hvis du ikke ser behovet for unik indeks på username og indeks på score, og samtidig bekymrer deg for ytelse bør du gå og legge deg til det går over :o)

 

Hvis du har fornuftig indeksering vil nok databasen regne ut dette raskere enn kode i php, men når du får mange brukere, og mange scores som både oppdateres og skal utregnes på en gang, vil databasen kunne bli en flaskehals, og det kan da være bedre å kjøre noen mindre intelligente spørringer i db og gjøre det smarte i php isteden.

Endret 15. november 2013 av quantum

[Fryth]

Hvis du ikke ser behovet for unik indeks på username og indeks på score, og samtidig bekymrer deg for ytelse bør du gå og legge deg til det går over :o)

 

Men hva er vitsen med å lage begge kolonnene til primary key, hvis det er det du mener..? Er man ikke like langt da? Og sparer man noe særlig på å legge brukernavn i en annen tabell? (Mulig jeg er helt på jordet, selvfølgelig. Usikker på hva en indeks på username/score vil gjøre med effektiviteten..)

Endret 15. november 2013 av Fryth

[quantum]

Hvorfor vil du ha flere like username i tabellen din? Hva er score for et ikke-unikt-brukernavn? Gjennomsnittet? Foreslår (igjen) at du bruker username som PK.

 

MySql kan vise deg hva indekser gjør med ytelsen hvis du spør med EXPLAIN PLAN, se http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

 

Hvis du ikke får mer enn noen få tusen brukere i tabellen din blir det nok mer et akademisk spørsmål hvordan du gjør dette, men det er nyttig å vite hvordan databasen nyttiggjør seg indeksering. Ikke bestandig det slår ut slik man tror...

 

Det som er viktig uansett, er å sette seg litt inn i databaseteori og forstå hvorfor det er en genuint dårlig ide å skippe primærnøkkel på tabeller.

[Fryth]

Det som er viktig uansett, er å sette seg litt inn i databaseteori og forstå hvorfor det er en genuint dårlig ide å skippe primærnøkkel på tabeller.

Absolutt. Grunnen til at jeg ikke har gjort username til PK er at da begrenser det til en score per bruker - noe jeg egentlig ikke ønsker da brukerne ikke (nødvendigvis) har et fast brukernavn, de bare taster inn der og da og sender inn. Hvertfall på dette tidspunktet, da må man jo lage et passord-system osv, for at folk skal reservere brukernavnet sitt. Om det blir større er dette selvfølgelig et alternativ.

[Crowly]

Hvis en bruker kan ha flere score, så må du opprette ett en til mange forhold mellom bruker og score, det krever en tabell for bruker og en for score. Siden en bruker (trolig) kan ha flere scores så bør du nok innføre ett nytt felt som forteller noe om denne score'en. Dette kan gi det økt fleksibilitet/funksjonalitet senere hvis du ønsker å summer på en type score.

 

Er bedre å få gjort "riktig" til å begynne med, er ofte mer jobb å endre på strukturen i etterkant når man vil legge til noe nytt.

[quantum]

Absolutt. Grunnen til at jeg ikke har gjort username til PK er at da begrenser det til en score per bruker - noe jeg egentlig ikke ønsker da brukerne ikke (nødvendigvis) har et fast brukernavn, de bare taster inn der og da og sender inn. Hvertfall på dette tidspunktet, da må man jo lage et passord-system osv, for at folk skal reservere brukernavnet sitt. Om det blir større er dette selvfølgelig et alternativ.

 

Så hvis flere brukere bruker samme brukernavn så vet man ikke hvilken score som tilhører hvem? Smart ...