[SQL]Refactoren lelijke query; query leesbaarder maken

[van.der.schulting]

Ik heb de volgende query:

code:

SELECT * FROM media WHERE id IN (SELECT id FROM media ORDER BY RAND() LIMIT 15) ORDER BY title

Helaas werkt deze query niet. Aangezien MYSQL geen LIMIT ondersteunt in een subquery, moest ik hem een beetje herschrijven mbv deze post. Dit resulteerde hierin:

code:

SELECT * FROM media WHERE id IN (SELECT * FROM (SELECT id FROM media ORDER BY RAND() LIMIT 15)alias) ORDER BY title

Anyhow, wat ik in feite wil is van is dus uit de tabel media 15 willekeurige records halen en deze vervolgens sorteren op title.

Toegegeven, dat lukt gewoon met bovenstaande query, maar ik vind deze query moeilijk te begrijpen. De eerste query is al een stuk beter te begrijpen, maar werkt helaas niet en ik vind ook de eerste query niet bepaald ideaal.

Kortom kan ik deze query wat 'leesbaarder' / simpeler maken? Het liefst zonder subquery.

[TRON]

Heb je deze query al geprobeerd?

code:

SELECT * FROM media ORDER BY RAND() LIMIT 15;

[van.der.schulting]

Heb ik niks aan, want dan sorteer je niet op title. Zoals ik al aangaf, ik wil 15 willekeurige records pakken en deze vervolgens sorteren op title.

[OnTracK]

Waarom doe je dit niet?

SQL:

SELECT *
FROM (
    SELECT * 
    FROM media 
    ORDER BY RAND() 
    LIMIT 15
) alias
ORDER BY title

Lijkt me ook nog effiiënter dan je eerste optie.

[Raymond P]

ehm?

edit: <knip> (ik ga slapen)

Maar ik denk dat je RAND() misschien beter in je code af kan handelen

[ Voor 34% gewijzigd door Raymond P op 22-03-2011 00:34 ]

[NMe]

OnTracK schreef op dinsdag 22 maart 2011 @ 00:01:
Waarom doe je dit niet?

SQL:

SELECT *
FROM (
    SELECT * 
    FROM media 
    ORDER BY RAND() 
    LIMIT 15
) alias
ORDER BY title

Lijkt me ook nog effiiënter dan je eerste optie.

Precies. Dat LIMIT niet werkt in subqueries klopt namelijk niet: het werkt niet in IN/ANY/ALL/SOME-subqueries. Wanneer je select uit de resultset van een subquery kun je dus gewoon een limit gebruiken, wat ook de reden is dat je dubbele subquery werkt.

[van.der.schulting]

Thanks, dat scheelt idd. Alleen toch jammer dat ik het met een subquery moet oplossen

Had stiekem gehoopt dat het zonder kon.

[NMe]

Waarom? Wat maakt een subquery nou uit? Deze query is maar marginaal langzamer dan alleen de subquery zou zijn en bovendien prima leesbaar. Wat wil je nog meer?

[van.der.schulting]

Het had op een wat nettere manier binnen het model (MVC) gepast als het geen subquery was geweest.
Verder is het idd een beetje mierenneuken

[NMe]

MVC legt helemaal geen restricties aan hoe diep je queries nesten?

[van.der.schulting]

Klopt als een bus, maar ik had het het liefst als volgt opgelost

code:

Model.find ....

In plaats van nu dus

code:

class Model {
  function find_fifteen_titles() {
    return query "SELECT * FROM ( SELECT * FROM media ORDER BY RAND() LIMIT 15) alias ORDER BY title"
  }
}

[NMe]

PHP:

class Model {
  function find($amount) {
    $amount = (int)$amount;
    return query "SELECT * FROM ( SELECT * FROM media ORDER BY RAND() LIMIT {$amount}) alias ORDER BY title"
  }
}

En hoe je query eruit ziet boeit voor deze functie natuurlijk ook niks, of het nou met subquery is of zonder.

[ Voor 22% gewijzigd door NMe op 22-03-2011 21:49 ]

[fleppuhstein]

SQL:

  SELECT *
    FROM media 
    ORDER BY 
      RAND(),
      title 
    LIMIT 15

Er is toch niks mis met primair order op de rand, om zo het willekeurig gedeelte op te lossen, en vervolgens op de title, gelijk de subquery verdwenen.

[hostname]

fleppuhstein schreef op dinsdag 22 maart 2011 @ 21:50:

SQL:

  SELECT *
    FROM media 
    ORDER BY 
      RAND(),
      title 
    LIMIT 15

Er is toch niks mis met primair order op de rand, om zo het willekeurig gedeelte op te lossen, en vervolgens op de title, gelijk de subquery verdwenen.

Alleen gaat dat niet werken. Elk item krijgt namelijk een andere waarde voor de RAND(), waardoor ze daar al op gesorteerd staat en ze niet meer op titel gesorteerd worden. Dit zou alleen werken mochten de eerste 15 items dezelfde waarde voor de RAND hebben.

[RobIII]

Wel weer zo'n echte MySQL-only oplossing; los van de syntax zou dit op MSSQL en AFAIK ook op Postgre en Oracle niet werken omdat de order by de rand maar 1 keer zou aanroepen en dus voor alle records dezelfde value returnen.
Euh, wacht effe... knik in m'n brein even uitstrijken
Nee, klopt toch min-of-meer wat ik zeg Dit geldt in ieder geval voor MSSQL. En daarbij is zo'n query in de meeste RDBMS'en (en dus ook MySQL) waarschijnlijk duur want het zal een full table scan als gevolg hebben om voor elke row een rand() te genereren.

[ Voor 57% gewijzigd door RobIII op 22-03-2011 22:26 ]

[NMe]

RobIII schreef op dinsdag 22 maart 2011 @ 22:14:
Wel weer zo'n echte MySQL-only oplossing; los van de syntax zou dit op MSSQL en AFAIK ook op Postgre en Oracle niet werken omdat de order by de rand maar 1 keer zou aanroepen en dus voor alle records dezelfde value returnen.
Euh, wacht effe... knik in m'n brein even uitstrijken
Nee, klopt toch min-of-meer wat ik zeg Dit geldt in ieder geval MSSQL. En daarbij is zo'n query in de meeste RDBMS'en (en dus ook MySQL) waarschijnlijk duur want het zal een full table scan als gevolg hebben om voor elke row een rand() te genereren.

LIMIT werkt sowieso niet in andere DBMS'en dan MySQL.

Verder: zelfs als het met ordering goed zou gaan als je subquery en main query omwisselt, dan nog zit je met een veel grotere resultset, dat performeert vast niet.

[RobIII]

NMe schreef op dinsdag 22 maart 2011 @ 22:26:
[...]

LIMIT werkt sowieso niet in andere DBMS'en dan MySQL.

Het ging mij om de Rand(), maar die blijkt in de meeste RDBMS'en ondersteund (ook MSSQL, dat wist ik niet; ik gebruik 't dan ook nooit )

[van.der.schulting]

NMe schreef op dinsdag 22 maart 2011 @ 21:48:
En hoe je query eruit ziet boeit voor deze functie natuurlijk ook niks, of het nou met subquery is of zonder.

Ik was wat onvolledig; ik werk met CakePHP. De functie Model.find is een standaard functie van CakePHP. Die kan ik dus niet gebruiken. Met jou oplossing override ik die functie
Daarvoor heb ik de functie Model.query nodig..

Anyhow, eigenlijk nutteloze details

[Camulos]

Houdt wel rekening mee dat de rand() functie in MySQL op een hele tabel echt langzaam werkt.

SQL:

ORDER BY RAND()
LIMIT 15

Hij zal eerst de hele tabel randomnizen en vervolgens de eerste 15 pakken.
Wanneer je tabel (tien)duizenden rijen bevat .. dan kan dit soms lang duren (denk aan seconden afhankelijk van het systeem).

Had dit probleem laatst een keer... had mijn query minstens 1000x sneller gemaakt door zelf random getallen te maken en deze in de WHERE-clause te zetten. Hiermee elimineer je de order-by en de limit

SQL:

WHERE primary-id = X OR primary-id = Y OR primary-id = Z

[ Voor 7% gewijzigd door Camulos op 23-03-2011 08:44 ]

[Woy]

Camulos schreef op woensdag 23 maart 2011 @ 08:42:
Houdt wel rekening mee dat de rand() functie in MySQL op een hele tabel echt langzaam werkt.

SQL:

ORDER BY RAND()
LIMIT 15

Hij zal eerst de hele tabel randomnizen en vervolgens de eerste 15 pakken.
Wanneer je tabel (tien)duizenden rijen bevat .. dan kan dit soms lang duren (denk aan seconden afhankelijk van het systeem).

Had dit probleem laatst een keer... had mijn query minstens 1000x sneller gemaakt door zelf random getallen te maken en deze in de WHERE-clause te zetten. Hiermee elimineer je de order-by en de limit

SQL:

WHERE primary-id = X OR primary-id = Y OR primary-id = Z

Maar dat werkt natuurlijk alleen als je weet dat al je id's sequentieel achter elkaar liggen zonder gaten ertussen.

[Camulos]

@Woy: idd... ik ga ervan uit dat er geen/weinig gaten zijn.

Wil je dat tegen gaan kun je bijvoorbeeld 20 random waarden nemen en dan limiten op 15 (maar dit werkt idd maar tot op zekere hoogte)

[ Voor 200% gewijzigd door Camulos op 23-03-2011 09:43 . Reden: In eerste instantie verkeerd geinterpreteerd ]

[kwaakvaak_v2]

van.der.schulting schreef op woensdag 23 maart 2011 @ 00:28:
[...]


Ik was wat onvolledig; ik werk met CakePHP. De functie Model.find is een standaard functie van CakePHP. Die kan ik dus niet gebruiken. Met jou oplossing override ik die functie
Daarvoor heb ik de functie Model.query nodig..

Anyhow, eigenlijk nutteloze details

Dus jij beweert dat je in cake niet de Model kan extenden met een eigen class en dan een myModel.find() kan uitvoeren..

[Killemov]

NMe schreef op dinsdag 22 maart 2011 @ 22:26:
[...]

LIMIT werkt sowieso niet in andere DBMS'en dan MySQL.

NIET waar. Beetje vervelend als een admin een leugen verkondigt.

[djexplo]

Killemov schreef op donderdag 24 maart 2011 @ 09:48:
[...]
NIET waar. Beetje vervelend als een admin een leugen verkondigt.

WEL waar . (MS SQL en Oracle SQL kennen limit b.v. niet)

Heb jij een bron, voorbeeld? (Zelf al gevonden PostgreSQL)

[ Voor 5% gewijzigd door djexplo op 24-03-2011 09:56 . Reden: Google ]

[NMe]

Killemov schreef op donderdag 24 maart 2011 @ 09:48:
[...]

NIET waar. Beetje vervelend als een admin een leugen verkondigt.

Beetje vervelend om voor leugenaar uitgemaakt te worden als a) het aannemelijk is dat er geen opzet in het spel is, b) admins ook mensen zijn en c) je niet vertelt waarom je denkt dat ik lieg.

[Killemov]

NMe schreef op donderdag 24 maart 2011 @ 10:40:
[...]

Beetje vervelend om voor leugenaar uitgemaakt te worden als a) het aannemelijk is dat er geen opzet in het spel is, b) admins ook mensen zijn en c) je niet vertelt waarom je denkt dat ik lieg.

Postgres kent het in ieder geval.

Je had de opmerking niet hoeven plaatsen. Als je niet de waarheid vertelt (opzet of niet) dan ... lieg je. En als admin, inderdaad ook maar een mensch, leg je daarbij nogal wat gewicht in de schaal.

[Verwijderd]

Killemov schreef op donderdag 24 maart 2011 @ 11:03:
[...]
Als je niet de waarheid vertelt (opzet of niet) dan ... lieg je.

lie·gen loog, h gelogen met opzet onwaarheden spreken: hij liegt dat hij barst heel erg; dat liegt er niet om dat moet je niet onderschatten

Overigens is alhier een mooie lijst van dit soort verschillen tussen DBMS'en. Postgres en MySQL zijn kennelijk de enige DBMS'en die LIMIT kennen.

[ Voor 22% gewijzigd door Verwijderd op 24-03-2011 13:01 ]

[NMe]

offtopic:
Gelukkig is alwetendheid geen requirement voor een functie als admin op een forum.

[Killemov]

Verwijderd schreef op donderdag 24 maart 2011 @ 12:58:
[...]
lie·gen loog, h gelogen met opzet onwaarheden spreken: hij liegt dat hij barst heel erg; dat liegt er niet om dat moet je niet onderschatten

Overigens is alhier een mooie lijst van dit soort verschillen tussen DBMS'en. Postgres en MySQL zijn kennelijk de enige DBMS'en die LIMIT kennen.

Sow, Oracle maakt er echt een crypto-hit van. En de standaard gewoon he-le-maal niet ondersteunen he.

[Woy]

Killemov schreef op donderdag 24 maart 2011 @ 22:40:
[...]

Sow, Oracle maakt er echt een crypto-hit van. En de standaard gewoon he-le-maal niet ondersteunen he.

Je kan natuurlijk meteen hoog van de toren blazen, en iemand voor een leugenaar uitmaken. Je kan natuurlijk ook gewoon zeggen dat er nog meer RDBMS'en zijn die het wel ondersteunen, maar dat het geen onderdeel van de SQL standaard is, en dat er meer systemen zijn die het niet ondersteunen dan wel ( Al zal het merendeel van de systemen wel een dergelijk mechanisme kennen ).

[Voutloos]

Oracle de schuld geven is tevens ook niet correct (een leugen ), want LIMIT zat er al jaren in.

[PrisonerOfPain]

Camulos schreef op woensdag 23 maart 2011 @ 08:42:
Hij zal eerst de hele tabel randomnizen en vervolgens de eerste 15 pakken.
Wanneer je tabel (tien)duizenden rijen bevat .. dan kan dit soms lang duren (denk aan seconden afhankelijk van het systeem).

Beetje jammer dat de optimizer dit niet begrijpt en gewoon random N elementen uit de index trekt.

[barfieldmv]

Ik zou vanuit de code een select id from tablename doen en die cachen (elke paar minuten updaten)
Vervolgens vanuit de code een random lijstje uit de id list pakken en dan dan 15 querien.

Met een cache dependency kan je je cache bij een data wijziging droppen en je kan je lijstje desnoods uitbreiden met de 15 laatste items voor als je hele random lijstje een keertje niet meer bestaat.

Dit stelt je meteen in staat om in de toekomst slimmere 'pak 15 willekeurige items' te pakken zodat er niet 2 keer achter elkaar dezelfde 'random' items in de lijst staan etc.