[Mysql] Random selecties van verschillende tabellen koppelen

[Gerwin]

Na nu enkele dagen met Google gezocht te hebben en alle mogelijke opzetten geprobeert te hebben kom ik niet uit volgend Mysql probleem.

Stel ik heb een 3 tal tabellen die ik op elkaar JOIN. Vervolgens wil ik random een rij krijgen na het feit dat deze tabellen aan elkaar gekoppeld zijn geweest.

Voor de eerste tabel (in dit geval Product) kan ik zonder gebruik te maken van RAND() zodat alles gelezen moet worden heel 'simpel' een record pakken middeld onderstaande mysql code. Echter hoe krijg ik nu de bijbehorende rows uit die andere tabellen erbij. Ik kan het wel zo krijgen dat hij de eerste row pakt, maar ik moet eigenijk 'random row uit tabel 1' en join met die ID random de records uit tabel 2 etc. Hopelijk is duidelijk wat ik bedoel. Is dit mogelijk, kan ik in een join een waarde gebruiken van een subquery in een vorige join?

SQL:

SELECT *
FROM
    `Product`

        JOIN (
            SELECT `r1`.`ProductID`
            FROM `ProductHoles` AS `r1` JOIN (SELECT (RAND() * (SELECT MAX(`ProductHolesID`) FROM `ProductHoles`)) AS `ProductHolesID`) AS r2
            WHERE `r1`.`ProductHolesID` >= `r2`.`ProductHolesID`
            ORDER BY `r1`.`ProductHolesID` ASC
            LIMIT 1) as `rows` ON (`rows`.`ProductID` = `Product`.`ProductID`)

research: http://jan.kneschke.de/projects/mysql/order-by-rand/

[ Voor 78% gewijzigd door Gerwin op 22-11-2007 17:02 . Reden: verduidelijkt ]

[Verwijderd]

Ik heb niet heel erg aandachtig je SQL bestudeerd... Maar ik zou 't denk ik zo zien:

je hebt een product tabel, waar je een random iets uit wil selecteren... Jij doet nu een join, ik zou zelf denk ik de boel in de where zetten. Zoiets als...

SQL:

SELECT *
FROM Product
JOIN <tabel.1> ON <conditie>
JOIN <tabel.2> ON <conditie>
JOIN <tabel.n> ON <conditie>
WHERE Product.ProductID IN (<je subquery/functie om een random ID te vinden>)

ofzo...

[Gerwin]

Verwijderd schreef op donderdag 22 november 2007 @ 17:20:
Ik heb niet heel erg aandachtig je SQL bestudeerd... Maar ik zou 't denk ik zo zien:

je hebt een product tabel, waar je een random iets uit wil selecteren... Jij doet nu een join, ik zou zelf denk ik de boel in de where zetten. Zoiets als...

SQL:

SELECT *
FROM Product
JOIN <tabel.1> ON <conditie>
JOIN <tabel.2> ON <conditie>
JOIN <tabel.n> ON <conditie>
WHERE Product.ProductID IN (<je subquery/functie om een random ID te vinden>)

ofzo...

Maar op deze manier krijg je toch nooit een random row uit tabel.1, tabel.2 en tabel.3?
De gedachte is wel interessant om het eens op deze manier te bekijken.

[Dido]

Ik snap echt niet wat je wilt. Wil je nou een random record uit tabel 1 met de bijbehorende rows uit tabel 2 en 3, of wil je gewoon 1 regel terug met een random record uit tabel 1, een random (dus niet gerelateerd) record uit tabel 2 en een radom record uit tabel 3

Ik kan je hele verhaal niet volgen als je stelt dat je ten eerste gaat joinen en vervolgens random waarden terug wilt krijgen. Waar join je dan op, en waarom in hemelsnaam?

[ATS]

Ik snap niet wat je bedoelt. Wat is nu de relatie tussen je drie tabellen? Wil je nu alleen een random record selecteren uit Table1, en verder aan dat record joinen wat er anders ook aan gejoined zou worden, of wil je op de een of andere manier ook wat er uit table2 en/of 3 komt random hebben?

edit:
Met Dido dus

[ Voor 4% gewijzigd door ATS op 22-11-2007 18:52 ]

[Gerwin]

Wat ik wil is het volgende:

SELECT * FROM tabel1 ORDER BY RAND() LIMIT 1;
en dan
SELECT * FROM tabel2 WHERE tabel1ID = {result eerste tabelid} ORDER BY RAND() LIMIT 1;
en dan
SELECT * FROM tabel3 WHERE tabel1ID = {result eerste tabelid} ORDER BY RAND() LIMIT 1;

Een random ID pakken uit die eerste tabel heb ik gefixt met:

SQL:

SELECT *
FROM
    `Product`

        JOIN (
            SELECT `r1`.`ProductID`
            FROM `ProductHoles` AS `r1` JOIN (SELECT (RAND() * (SELECT MAX(`ProductHolesID`) FROM `ProductHoles`)) AS `ProductHolesID`) AS r2
            WHERE `r1`.`ProductHolesID` >= `r2`.`ProductHolesID`
            ORDER BY `r1`.`ProductHolesID` ASC
            LIMIT 1) as `rows` ON (`rows`.`ProductID` = `Product`.`ProductID`)

Maar hoe laat je aan de JOIN op tabel2 en tabel3 weten wat de uitkomst van de ID (bovenstaande) geweest is om vervolgens daar op een vergelijkbare manier weer random een record uit te pakken?

Kortgezegt wil ik dus inderdaad random een record uit Tabel1. En de andere tabellen JOINEN, maar het record uit die andere tabellen moet ook beide random zijn. Als ik een simpele join doe dan is de volgorde altijd gelijk.

Ik wil voorkomen dat als ik bijvoorbeeld 100.000 recods heb ik de tabellen een RAND() order problemen en traagheid gaat geven. Vandaar dat ik ook op deze manier een ID pak uit Tabel1. Maar hoe dit 'nog een keer' te doen, maar dan met een extra conditie (van de vorige join)?

[Dido]

En wat gebeurt er dan met

SQL:

SELECT a.blaat, b.foo, c.bar, rand()
FROM tabel1 a
JOIN tabel2 b on b.tabel1ID=a.ID
JOIN tabel3 c on c.tabel1ID=a.ID
ORDER BY 4
LIMIT 1

Je haalt dan alle records uit tabel1 op met alle gejoinde records uit tabel 1 en twee, die je vervolgens @ random sorteert. Daarvan pak je de eerste.

e.e.a. onder voorbehoud, ik gebruikt eigenlijk nooit RAND of LIMIT in SQL. Daar heb ik applicaties voor

of misschien zo, iets meer zoals jij het deed:

SQL:

SELECT *
FROM tabel1 a
JOIN tabel2 b on b.tabel1ID=a.ID
JOIN tabel3 c on c.tabel1ID=a.ID
ORDER BY RAND() LIMIT 1

Doet als ik het goed heb hetzelfde.

[ Voor 19% gewijzigd door Dido op 22-11-2007 20:11 ]

[Gerwin]

Dido, laten we voor dit voorbeeld aannemen dat alle tabellen 1.000.000 records bevatten.
In je eerste voorbeeld moet dan niet alles gelezen worden en gaat de server voor alle drie de tabellen 1.000.000 random nummers genereren en die allemaal in tmp tables op de disk wegschrijven?

Het tweede is precies wat ik tot nu had. Dat geeft veel problemen. De server genereerd voor elke tabel voor elke row random nummers en slaat deze op etc. Dit kan uitvoer tijden van minuten opleveren...

Je zegt dat je applicaties hebt voor RAND en LIMIT query's en nooit RAND en LIMIT gebruikt?
Van RAND kan ik me dat voorstellen, dat kan je wel orderen als je extern de hele meuk sorteert.
Maar LIMIT kun je toch niet zonder? Of je moet al 100% zeker zijn dat je altijd precies de juiste hoeveelheid records terugkrijgt.

[Dido]

Gerwin schreef op donderdag 22 november 2007 @ 20:38:
Dido, laten we voor dit voorbeeld aannemen dat alle tabellen 1.000.000 records bevatten.
In je eerste voorbeeld moet dan niet alles gelezen worden en gaat de server voor alle drie de tabellen 1.000.000 random nummers genereren en die allemaal in tmp tables op de disk wegschrijven?

Het tweede is precies wat ik tot nu had. Dat geeft veel problemen. De server genereerd voor elke tabel voor elke row random nummers en slaat deze op etc. Dit kan uitvoer tijden van minuten opleveren...

Ja, jij bent degene die als eis stelt dat je een willekeurig (hell, ik ben nog nooit een businesscase tegengekomen om dat te doen, maar goed) record uit een totaal van 10¹⁸ records terug wilt hebben.

Je kunt het applicatief versnellen door het niet in SQL te doen, of in stappen: voer inderdaad 3 queries uit, dan ben je een stuk sneller klaar waarschijnlijk. Maar kennelijk wou je dat niet, want die opite zette je zelf al ongeveer neer hierboven.

Je zegt dat je applicaties hebt voor RAND en LIMIT query's en nooit RAND en LIMIT gebruikt?
Van RAND kan ik me dat voorstellen, dat kan je wel orderen als je extern de hele meuk sorteert.
Maar LIMIT kun je toch niet zonder? Of je moet al 100% zeker zijn dat je altijd precies de juiste hoeveelheid records terugkrijgt.

Er is geen juiste hoeveelheid records. Of je wilt 1 specifiek record terug, of je wilt alle records terug die aan een bepaald criterium voldoen.
Althans, dat is mijn beleving.

En in voorkomende gevallen is er nog zoiets als een cursor. Erg handig in embedded SQL. Maar goed, dan ga ik terug naar COBOL

[Gerwin]

Ik sta er wel eens meer om bekend iets te willen wat heel moelijk is te realiseren. Maar random iets uit een grote bak halen komt toch wel meer voor in de praktijk dan je denkt hoor. Denk aan een webshop die op de frontpage gewoon een aantal willekeurige producten wil tonen. Of een bannersysteem die random een banner moet weergeven. Of zoiets als GoogleAds, Random url, met een titel en txt.

[Confusion]

Dido schreef op donderdag 22 november 2007 @ 20:08:
e.e.a. onder voorbehoud, ik gebruikt eigenlijk nooit RAND of LIMIT in SQL. Daar heb ik applicaties voor

Het is vaak erg inefficient om veel data over te pompen, om vervolgens zeg 95% te negeren. Datatransport kost tenslotte ook tijd en als je website responsief moet zijn, kan je het je niet veroorloven even 10 MB per query over te pompen.

Daarnaast kan een database sneller RAND'en dan je eigen methode (mits je de database op een fatsoenlijk manier gebruikt; je kan jezelf ook verneuken, zoals dat in de startpost gelinkte artikel laat zien).

Gerwin schreef op donderdag 22 november 2007 @ 21:57:
Ik sta er wel eens meer om bekend iets te willen wat heel moelijk is te realiseren. Maar random iets uit een grote bak halen komt toch wel meer voor in de praktijk dan je denkt hoor. Denk aan een webshop die op de frontpage gewoon een aantal willekeurige producten wil tonen. Of een bannersysteem die random een banner moet weergeven. Of zoiets als GoogleAds, Random url, met een titel en txt.

Wat veel gebeurt is dat er niet telkens opnieuw gerandomiseerd wordt, maar dat bijvoorbeeld een tabel een kolom met random getallen heeft waar op geordend wordt. Die kolom wordt dan elke dag, of elk uur, ververst. Dan hoef je niet in iedere query nieuwe random getallen te genereren; dat is vaak gewoon niet nodig.

[ Voor 38% gewijzigd door Confusion op 22-11-2007 22:15 ]

[Dido]

Confusion schreef op donderdag 22 november 2007 @ 22:12:
Het is vaak erg inefficient om veel data over te pompen, om vervolgens zeg 95% te negeren. Datatransport kost tenslotte ook tijd en als je website responsief moet zijn, kan je het je niet veroorloven even 10 MB per query over te pompen.

Wie zegt dat ik voor websites ontwikkel?

[Gerwin]

Dido schreef op vrijdag 23 november 2007 @ 00:08:
[...]

Wie zegt dat ik voor websites ontwikkel?

Ook als je niet voor websites ontwikkeld moet het toch beter zijn zo weinig mogelijk data 'op te vragen'. Als jij op tien tabellen met miljoen records gewoon opvraagt en die softwarematig gaat ramdomicen, dan moet je alles in het geheugen of wegschrijven in tmp bestandjes. Dit terwijl het ook mogelijk is om een eisen te stellen aan de data waarmee je wilt werken.

Meer specifiek terug naar het topic. De oplossing heb ik nog niet in zicht, ook heb ik geen aanwijzing in welke kant ik moet kijken voor een oplossing. Zoals ik eerder schreef heb ik een mogelijkheid om zo optimaal mogelijk willekeurig één record uit één tabel te selecteren. Echter heb ik geen mogelijkheid om willekeurig een bijbehorend record uit andere tabellen te halen. Nu ik er verder over nadenk moet uiteindelijk in die willekeurige selectie uit die eerste tabel natuurlijk ook een controle gedaan worden of er ook wel records bestaan die horen bij die eerste tabel.

Tabellen:

Product
ProductID (int)
Product

Product2Title
ProductID (int)
TitleID (int)

Product2Description
ProductID (int)
DescriptionID (int)

Kort de vragen:

1. Hoe selecteer ik zonder tmp-tables zo optimaal mogelijk een willekeurig record uit tabel 'Product' waarvan de ProductID bestaat in de tabel 'Product2Title' en 'Product2Description'?
2. Hoe selecteer ik als ik het willekeurig record heb uit 'Product' willekeurig een bijbehorend record uit tabel 'Product2Title' en 'Product2Description'?

Wat ik eigenlijk wil is dus het volgende:

SQL:

SELECT Product, TitleID, DescriptionID
FROM Product, Product2Title, Product2Description
WHERE
Product.ProductID = Product2Title.ProductID AND
Product.ProductID = Product2Description.ProductID
ORDER BY RAND()
LIMIT 1

Alleen loopt dit in de soep als er veel records zijn. Ik zoek een elegantere oplossing.

[ Voor 10% gewijzigd door Gerwin op 23-11-2007 01:59 ]

[ATS]

Waarom doe je het niet in stappen? Haal eerst je product ID op met behulp van je ORDER BY RAND() of - misschien sneller? - door te sorteren op je ID en met behulp van LIMIT. Gebruik dan dit ProductID voor je query hierboven.
Aangezien je producttabel waarschijnlijk toch niet de hele wijzigt kan je de COUNT gewoon cachen, zodat je betrouwbaar een willekeurig record kan kiezen.

[Dido]

Gerwin schreef op vrijdag 23 november 2007 @ 01:09:
Ook als je niet voor websites ontwikkeld moet het toch beter zijn zo weinig mogelijk data 'op te vragen'. Als jij op tien tabellen met miljoen records gewoon opvraagt en die softwarematig gaat ramdomicen, dan moet je alles in het geheugen of wegschrijven in tmp bestandjes. Dit terwijl het ook mogelijk is om een eisen te stellen aan de data waarmee je wilt werken.

Klopt natuurlijk. Maar als ik in een I/O module tegen DBII aan zit te praten op een mainframe is de kans klein dat ik ueberhaupt willekeurige data nodig heb, en de genoemde cursors zorgen ervoor dat ik naar believen door de "recordset" heen kan lopen.

Meer specifiek terug naar het topic. De oplossing heb ik nog niet in zicht, ook heb ik geen aanwijzing in welke kant ik moet kijken voor een oplossing. Zoals ik eerder schreef heb ik een mogelijkheid om zo optimaal mogelijk willekeurig één record uit één tabel te selecteren. Echter heb ik geen mogelijkheid om willekeurig een bijbehorend record uit andere tabellen te halen.

Waarom heb je dat dan niet
Je kunt toch parameters meegeven in een query, hoop ik?

Dus (pseudocode):

SQL:

SELECT id FROM tabel1 ORDER BY rand() LIMIT 1

code:

myvar = id

SQL:

SELECT * FROM tabel2 WHERE t1_id = myvar ORDER BY rand() LIMIT 1

SQL:

SELECT * FROM tabel3 WHERE t1_id = myvar ORDER BY rand() LIMIT 1

Nu ik er verder over nadenk moet uiteindelijk in die willekeurige selectie uit die eerste tabel natuurlijk ook een controle gedaan worden of er ook wel records bestaan die horen bij die eerste tabel.

Kijk, en daar ga je dan wel de mist in.

Immers, om te weten of er data bij de records uit tabel 1 bestaat moet je toch door je andere tabellen heen. En omdat er geen daadwerkelijke link is tussen records in tabel2 en tabel3 ga je altijd een cartesisch product krijgen als je ze samen in een query gooit en ze gaat joinen (of je dat nou expliciet doet, of impliciet, zoals je zelf aangaf).

Je zou misschien iets kunnen proberen als

SQL:

SELECT a.id
  FROM tabel1 a
WHERE EXISTS (SELECT b.id
                FROM tabel2 b
               WHERE b.t1_id = a.id)
  AND EXISTS (SELECT c.id
                FROM tabel3 c
               WHERE c.t1_id = a.id)
ORDER BY rand()
LIMIT 1

Ongetest en zo, maar je snapt het idee. Geen idee of dit veel sneller gaat, maar het zou moeten, omdat maar 1 matching record uit tabel 2 en 3 wordt gelezen.
Na deze query moet je dan uiteraard nog je gegevens uit tabel 2 en 3 ophalen. Dat gaat niet in 1 query lukken, omdat je er dan nooit aan ontkomt een keuze te maken uit 10^18 mogelijke records.

[Voutloos]

Of je zorgt gewoon dat elk product minimaal 1 titel en minimaal 1 beschrijving heeft. En in een gemiddeld datamodel is er ook max 1 titel en beschrijving en kan het gewoon in 1 tabel zitten.

Anyway, zelfs al geeft iemand de ideale oplossing (en ORDER BY RAND() icm wazige joins staat heel hoog in menig mysql dont-lijstje), blijf je imo zitten met een veel groter probleem waarom veel queries te lastig uitvallen: Je datamodel stinkt.

[Gerwin]

Dido, natuurlijk kan ik variabelen meegeven in een query. Echter ontgaat me even hoe de result van een subquery in een andere subquery gebruikt kan worden... De query die je geeft doet op zich wat het idee is. Moet nadien zoals je schrijft wel afzonderlijk de andere tabellen gelezen worden, maar dat is ge overkomen. Echter gaat bij deze query alles weer in een tmp-table op de disk. Dat wil ik juist voorkomen en geeft op dit moment veel problemen, er komen queryś van 20 seconden en dat is niet de bedoeling. Op zich toch vreemd, hoort mysql niet gebruik te maken van de bestaande index 'ProductID'? Juist om dat 'swappen' te voorkomen had ik van deze website. De volgende query bedacht. Alleen zit hier dan weer geen check in naar die andere tabellen. Ook de 'gaten in de nummering' is een probleem. Dan moet ie eigenlijk een soort LOOP doen totdat er een geldige ProductID geselecteerd is.

SQL:

SELECT *
FROM
`Product`
JOIN (
SELECT `r1`.`ProductID`
FROM `ProductHoles` AS `r1` JOIN (SELECT (RAND() * (SELECT MAX(`ProductHolesID`) FROM `ProductHoles`)) AS `ProductHolesID`) AS r2
WHERE `r1`.`ProductHolesID` >= `r2`.`ProductHolesID`
ORDER BY `r1`.`ProductHolesID` ASC
LIMIT 1) as `rows` ON (`rows`.`ProductID` = `Product`.`ProductID`)

Voutloos, datamodel kan stinken, maar de bedoeling is toch een aantal één-op-meer relaties te krijgen, en dan random een samengestelde row te selecteren. Zoals ik eerder zei, dit komt toch wel meer voor hoor. Denk aan Google Adwords, die selecteren ook urls met verschillende stukjes tekst en dan random.

Naast het werken met 3 tabellen en de koppeltabellen is me overigens wel door mijn hoofd geschoten om misschien het handig is alles in één grote koppeltabel te gooien met alle mogelijke combinaties. Maar ik weet nu niet of ik daar goed aan doe om die weg in te slaan. Tis niet echt genormaliseerd op die manier, en ik moet dan natuurlijk wel alles (dubbel) opslaan. Daarintegen kun je in theorie makkelijker een random row pakken uit 1 tabellen dan drie keer random uit drie tabellen die ook nog eens aan esen moeten voldoen.

[ Voor 1% gewijzigd door Gerwin op 23-11-2007 21:06 . Reden: url toegevoegd ]

[ATS]

Even een vraagje: Als ik het goed begrijp zitten er in tabel 2 en 3 meerdere records die allemaal linken aan een enkel record in tabel 1, toch? Over hoeveel records hebben we het dan eigenlijk helemaal? Ik bedoel, zullen dat er in de praktijk 5, 50, 500 of 500.000 zijn? Even voor de duidelijkheid: ik bedoel niet het totale aantal records in die tabel, maar het aantal wat aan die ProductID hangt. Als het een van de eerste opties is, waarom doe je dan zo moeilijk? Dan kan je ze net zo goed allemaal binnen halen in je applicatie, en dáár een random keuze maken. Dat is in elk geval veel goedkoper dan een tijdelijke tabel laten aanmaken door MySQL.

Ik zie overigens echt niet in waarom zo'n tijdelijke tabel zou ontstaan als je in aparte queries je resultaten opvraagt. Misschien eens een explain doen in kijken waarom hij er op staat om dat te doen? Indexje vergeten ergens waardoor hij eerst elk record van een random nummer wil voorzien in plaats van eerst een selectie te maken?

[Confusion]

Gerwin schreef op vrijdag 23 november 2007 @ 17:40:
Denk aan Google Adwords, die selecteren ook urls met verschillende stukjes tekst en dan random.

Yup, maar die selecteren random 1 van pakweg 100 betaalde links die bij 1 random geselecteerd Adword (van de 10 relevante Adwords) horen. Dat is nogal iets anders dan 2 miljoen rijen ophalen, daar random getallen aan koppelen (want RAND() genereert een random getal voor iedere rij!) en dan kiezen. Dat Google dat een miljoen keer doet maakt niet uit: als je query snel is, dan kan je met meer servers de hoeveelheid opvangen. Een trage query krijg je nooit snel met meer servers.

[ Voor 15% gewijzigd door Confusion op 23-11-2007 19:52 ]

[Dido]

Gerwin schreef op vrijdag 23 november 2007 @ 17:40:
Echter gaat bij deze query alles weer in een tmp-table op de disk. Dat wil ik juist voorkomen en geeft op dit moment veel problemen, er komen queryś van 20 seconden en dat is niet de bedoeling.

Dit stukje zorgt ervoor dat ik je hele post nauwelijks snap.
Welke query zorgt nu voor die tmp-tables?

offtopic:
Wat meer enters in je code-blocks maakt trouwens je hele post een stuk leesbaarder

[Gerwin]

ATS schreef op vrijdag 23 november 2007 @ 17:51:
Even een vraagje: Als ik het goed begrijp zitten er in tabel 2 en 3 meerdere records die allemaal linken aan een enkel record in tabel 1, toch? Over hoeveel records hebben we het dan eigenlijk helemaal? Ik bedoel, zullen dat er in de praktijk 5, 50, 500 of 500.000 zijn? Even voor de duidelijkheid: ik bedoel niet het totale aantal records in die tabel, maar het aantal wat aan die ProductID hangt. Als het een van de eerste opties is, waarom doe je dan zo moeilijk? Dan kan je ze net zo goed allemaal binnen halen in je applicatie, en dáár een random keuze maken. Dat is in elk geval veel goedkoper dan een tijdelijke tabel laten aanmaken door MySQL.

Ik zie overigens echt niet in waarom zo'n tijdelijke tabel zou ontstaan als je in aparte queries je resultaten opvraagt. Misschien eens een explain doen in kijken waarom hij er op staat om dat te doen? Indexje vergeten ergens waardoor hij eerst elk record van een random nummer wil voorzien in plaats van eerst een selectie te maken?

In Tabel 1 zit één record die verwijst naar zegge 10-20 records in Tabel 2, 3, ... Wat er ik nu heb is dat ik alles op elkaar JOIN en dan RAND(). De hoeveelheid records in Tabel 1 kun je uitgaan van 'veel', ik wil in de toekomst de zaak kunnen uitbreiden en dus moet alles gewoon ook bij 5 miljoen werken zegmaar. Vandaar dat in in beginsel gezocht heb naar een mogelijkheid om een enkele record uit de database te pakken en tevens het feit te tackelen dat de ProductID's niet altijd opeenvolgend zijn, er zal wel eens een record verwijderd worden bijvoorbeeld.

Deze query werkt prima voor één record, via een andere tabel waarin alle ProductID's staan en een ander ID zonder gaten selecteert mysql een ID en pakt via die weg het ProductID.

SQL:

SELECT *
FROM
`Product`
JOIN (
SELECT `r1`.`ProductID`
FROM `ProductHoles` AS `r1` JOIN (SELECT (RAND() * (SELECT MAX(`ProductHolesID`) FROM `ProductHoles`)) AS `ProductHolesID`) AS r2
WHERE `r1`.`ProductHolesID` >= `r2`.`ProductHolesID`
ORDER BY `r1`.`ProductHolesID` ASC
LIMIT 1) as `rows` ON (`rows`.`ProductID` = `Product`.`ProductID`)

Het idee om alles toch maar binnen te halen in de (php) applicatie geeft me niet zo'n goed gevoel. Stel dat ik uiteindelijk met een paar miljoen combinaties zit in te toekomst, dan is het toch niet handig op via een php applicatie die te randomicen en een record te pakken?

Mysql maakt volgens mij een tijdelijke tabel aan omdat hij in de voorbeelden tot id's voor elk record moet genereren en die tijdelijk moet opslaan, om het vervolgens op dat id te ordenen in het geheugen. Dit kost veel resources. Het is toch niet mogelijk om random een selectie te maken zonder dat mysql elk record van een random nummer voorziet? Om deze reden was ik gekomen met het MAX(ProductHoledID) idee hierboven.

Confusion schreef op vrijdag 23 november 2007 @ 19:52:
[...]

Yup, maar die selecteren random 1 van pakweg 100 betaalde links die bij 1 random geselecteerd Adword (van de 10 relevante Adwords) horen. Dat is nogal iets anders dan 2 miljoen rijen ophalen, daar random getallen aan koppelen (want RAND() genereert een random getal voor iedere rij!) en dan kiezen. Dat Google dat een miljoen keer doet maakt niet uit: als je query snel is, dan kan je met meer servers de hoeveelheid opvangen. Een trage query krijg je nooit snel met meer servers.

Per account selecteren die uit 1 van de 100 betaalde links (er zullen overigens ook wel klanten zijn die meer en grotere accounts hebben). Echter tussen alle 'miljoenen' klanten moet toch ook random een advertentie bepaald worden? Meer servers en beter verdelen lost het inderdaad wel op, maar om alles op zo'n grote schaal te maken met duizenden servers voor een simpele random selectie gaat me ook een beetje te ver.

Dido schreef op vrijdag 23 november 2007 @ 20:21:
[...]

Dit stukje zorgt ervoor dat ik je hele post nauwelijks snap.
Welke query zorgt nu voor die tmp-tables?

offtopic:
Wat meer enters in je code-blocks maakt trouwens je hele post een stuk leesbaarder

Je voorbeeld doet bij mij het volgende:

code:

id select_type table    type                possible_keys key                 key_len             ref               rows    Extra
1  PRIMARY     Product  ALL                 NULL          NULL                NULL                NULL              10000   Using where; Using temporary; Using filesort
3  DEPENDENT   SUBQUERY Product2Title       ref           Product2Title       2                   Product.ProductID 1       Using index
2  DEPENDENT   SUBQUERY Product2Description ref           Product2Description 2                   Product.ProductID 1       Using index

De hele query is al ruim 7 seconden bezig.

[Dido]

Die subqueries zijn dus het probleem niet, daar gebruikt hij gewoon een index om 1 record terug te geven.

Je main SELECT doet echter moeilijk, vanwege, jawel: je rand(). Hij moet alle records uit je tabel1 lezen, er een random getal achter hangen, en de hele recordset sorteren. Dat gebeurt dus in een tijdelijke tabel, en dat kost een hoop tijd.

offtopic:
Jammer dat mijn vorige off-topic volledig genegeerd is. Vind je zelf je post leesbaar

[ Voor 12% gewijzigd door Dido op 23-11-2007 23:35 ]

[Gerwin]

Aggr, heeft hij de tabs niet overgenomen. Heb de code nu leesbaarder gemaakt.

Hij lees inderdaad alle rows uit, genereerd nummers voor elke row die uit de JOINs komt. Heb je dus een join die uiteindelijk 10.000 of een paar miljoen rows genereerd dan gaat hij voor alle rows een nummer maken, slaat alles op, sorteert alles, om vervolgens slechts 1 record terug te geven.

Voor dit probleem had ik een oplossing gevonden op het web. Dat had ik in de eerste post al geschreven en daarna meerdere keren herhaald:

SQL:

SELECT *
FROM
`Product`
JOIN (
SELECT `r1`.`ProductID`
FROM `ProductHoles` AS `r1` JOIN (SELECT (RAND() * (SELECT MAX(`ProductHolesID`) FROM `ProductHoles`)) AS `ProductHolesID`) AS r2
WHERE `r1`.`ProductHolesID` >= `r2`.`ProductHolesID`
ORDER BY `r1`.`ProductHolesID` ASC
LIMIT 1) as `rows` ON (`rows`.`ProductID` = `Product`.`ProductID`)

Echter heb ik in dit geval slechts random een row uit één tabel. Mijn idee was om de ID die terugkomt uit deze query te gebruiken in een volgende join die enkel de rows gebruikt en randomized waarvan ook daadwerkelijk de ID uit de eerste tabel bestaat. Op die manier selecteerd hij bijvoorbeeld slechts de 20 titels van ProductID uit de eerste tabel en mixt die door elkaar.

Mijn eerste vraag was dan ook hoe ik in een volgende join die waarde die terugkomt uit de eerdere join kan gebruiken. Daarnaast wil ik in die eerdere join natuurlijk wel zeker zijn dat er een waarde terugkomt waar de volgende joins ook echt iets mee kunnen.

[Dido]

Op allebei die vragen heb je toch al een antwoord

Hoe gebruik je je resultaat in een volgende query: Dido in "[Mysql] Random selecties van verschillen..."
Hoe zorg je dat je uit qury 1 alleen bruikbare rijen krijgt: Dido in "[Mysql] Random selecties van verschillen..."

Als jouw query die je herhaaldelijk zonder verdere uitleg post, sneller is dan

SQL:

SELECT id FROM table1 ORDER BY rand() LIMIT 1

moet je hem vooral gebruiken.

Het gegoochel wat daarin plaatsvindt is echter een recept voor bagger. Sowieso zit er op het eerste gezicht een JOIN zonder expliciete restrictie in.

Maar goed, bouw in die query de twee where's in die ik gaf en kijk of het werkt?

Uit de explain begrijp ik dat de subqueries het probleem niet zijn, namelijk.

edit:
Als ik nog eens even kijk naar die query die jij post is hij trouwens erg omslachtig, maar er zit wel een leuk idee in. Dat idee is echter niet onfeilbaar als je "bruikbare records terug wilt hebben.

Doet deze query niet ongeveer hetzelfde?

SQL:

SELECT id 
  FROM tabel1 
 WHERE id >= (SELECT rand() * max(id) FROM tabel1)
LIMIT 1

Het probleem zit hem er natuurlijk nu alleen in dat je een "bruikbare" ID wilt hebben en dat gaat niet luken als je rand()*max(id) naar een onbruikbaar id verwijst en er geen hogere id's zijn die bruikbaar zijn.

offtopic:
Tabs zijn geen enters. Mensen moeten horizontaal scrollen om jouw posts te kunnen lezen, en dt is op zijn zachtst gezegd hoogst irritant. Laat ik het zo stellen: de volgende post van jou die breder is dan de standaard forumlayout ga ik niet eens meer proberen te lezen.

[ Voor 20% gewijzigd door Dido op 24-11-2007 11:56 ]

[Dido]

Maar even een nieuwe, ik blijf editten

In mijn laatste query weet ik niet zeker of hij stopt na het eerste record (vanwege de limit 1 zonder sort zou het kunnen dat hij zo slim is, maar ik vertrouw niet op intelligentie van MySQL )
Als hij dat niet doet gaat het weer niet zo snel, want dan gaat hij voor ieder record in je tabel een rand()*max(id) berekenen en dat schiet niet op.

Dan kom ik toch weer uit bij een semi-applicatieve oplossing - puur vanuit performance-oogpunt:

SQL:

SELECT rand() * max(id) as Foo from tabel1

code:

MyVar = Foo

SQL:

SELECT a.id as Bar
  FROM tabel1 a
WHERE EXISTS (SELECT b.id
                FROM tabel2 b
               WHERE b.t1_id = a.id)
  AND EXISTS (SELECT c.id
                FROM tabel3 c
               WHERE c.t1_id = a.id)
  AND a.id >= MyVar
LIMIT 1

code:

MyOtherVar  = Bar

SQL:

SELECT * FROM tabel2 where t1_id = MyOtherVar;
SELECT * FROM tabel3 where t1_id = MyOtherVar;

De query met de twee EXISTs zou nu ook gewoon moeten performen omdat je van die ORDER BY rand() afbent.

Het enige probleem is natuurlijk nog steeds dat je de kans loopt dat je geen bruikbaar record terugkrijgt als je random ID te hoog is. Dat merk je vanzelf omdat je niets uit de tweede select terugkrijgt. Dan ga je terug naar af.

Een tweede is dat ik niet denk dat je nu echt 100% random zit te kiezen, maar ik weet niet of dat echt noodzakelijk is.

Toch nog een edit:
Je kunt willen blijven zoeken naar 1 query die het oplost, maar daar waren we al uit: omdat er geen verband te leggen is tussen tabel 2 en tabel 3 krijg altijd een cartesisch product als je ze in 1 join-constructie gooit. En dat wil je niet, dus wil je het niet in 1 query doen.

[ Voor 9% gewijzigd door Dido op 24-11-2007 12:12 ]

[Gerwin]

Mijn excuses voor de wat late reactie in dit topic. Ik heb van alles getest met het voorbeeld dat Dido gaf. Ik kom hier toch verder mee dan ik dacht. Mijn vermoeden is dat ik in gedachten het van een hele verkeerde manier aan het benaderen ben. Zoals in het voorbeeld van Dido lukt het inderdaad random een ID te selecteren uit ProductHoles tabel (hier staan opeenlopende ID's in en daaraan gekoppeld de ProductID's die niet altijd oplopend zijn). Random kan ik daar een ProductID uit selecteren. Ook het nadien koppelen aan dat Product via PHP en nieuwe query gaat prima en performed perfect. Ook kijken middels EXISTS gaat prima, ik gebruikte voorheen het commando IN, en in denk dat hij dan echt de gehele tabel moet doorlopen.

Het volgende probleem kom ik echter wel tegen. Dido, hoe selecteer jij uit die eerste tabel waar je volgende doet:

SQL:

SELECT rand() * max(id) as Foo from tabel1

enkel de id's die ook daadwerkelijk door de volgende query komen:

SQL:

SELECT a.id as Bar
  FROM tabel1 a
WHERE EXISTS (SELECT b.id
                FROM tabel2 b
               WHERE b.t1_id = a.id)
  AND EXISTS (SELECT c.id
                FROM tabel3 c
               WHERE c.t1_id = a.id)
  AND a.id >= MyVar
LIMIT 1

Hoe pak je een random id uit tabel1 die EXISTS relaties heeft in tabel2, tabel3, etc.?
Ik begrijp dat die dus niet altijd het geval is in bovenstaand voorbeeld.
Kan mysql niet gewoon die WHERE conditie meenemen in de rendering van de MAX(id)?
Of zeg ik nu iets wat helemaal niet mogelijk is? Moet je dan echt in PHP de query laten loopen
totdat er een geldig ID terugkomt? Dat is ook niet optimaal toch?

[ATS]

Je weet pas wat optimaal is als je het gaat testen in de praktijk. Te vroeg optimaliseren is absoluut niet handig.

Verder helpt dit je misschien op weg? Is pseudo SQL, maar je snapt het idee misschien:

SQL:

SELECT a.id
  FROM tabel1 AS a
LEFT JOIN tabel2 AS b 
  ON (a.id = b.t1_id)
GROUP BY a.id 
HAVING COUNT(b.id)>0
ORDER BY rand()
LIMIT 1

Geen idee hoe snel dit is trouwens in jouw geval. Het idee is dat je met behulp van een join kijkt waar die join ook resultaat heeft.

[ Voor 9% gewijzigd door ATS op 04-12-2007 20:07 ]

[Gerwin]

ATS schreef op dinsdag 04 december 2007 @ 20:05:
Je weet pas wat optimaal is als je het gaat testen in de praktijk. Te vroeg optimaliseren is absoluut niet handig.

Verder helpt dit je misschien op weg? Is pseudo SQL, maar je snapt het idee misschien:

SQL:

SELECT a.id
  FROM tabel1 AS a
LEFT JOIN tabel2 AS b 
  ON (a.id = b.t1_id)
GROUP BY a.id 
HAVING COUNT(b.id)>0
ORDER BY rand()
LIMIT 1

Geen idee hoe snel dit is trouwens in jouw geval. Het idee is dat je met behulp van een join kijkt waar die join ook resultaat heeft.

Juist, nu alles toch weer in één query. In een iets andere setting had ik in begin hetzelfde. Het probleem waar ik bij deze query tegenaan loop is dat als ik 'veel' records heb ik alle tabellen die allemaal aan elkaar gekoppeld moeten worden en dan allemaal van door mysql gemixed worden met RAND() dan geeft dat een gigantische bottleneck. Neem bijvoorbeeld aan dat tabel1 500.000 records heeft, tabel2 heeft er nog eens 500.000 en tabel3 heeft er nog eens 500.000. Tabel2 gekoppeld aan tabel1, tabel3 tevens gekoppeld aan tabel1; beide met een (tussen)koppeltabel. Dan gaat hij ze eerst joinen 500.000 maal 500.000 maal 500.000 komen er 250.000.000.000 recods uit die allemaal een ID random een ID toegekend krijgen, gaan in een tmp table, alles word georderd, en er rolt 1 record uit. Execution time: 30 minuten?

Om deze reden was ik overgestapt de structeur om de ID's van tabel1 in tabel1holes te zetten. Tabel1 heeft geen oplopende nummers en er kan dus niet random een nummer geselecteerd worden tussen 1 en het hoogste getal. Als ik de ID's in tabel1holes zet met afzonderlijk wel een oplopende nummering dan kan ik daar wel relatief simpel een nummer tussen de 1 en het hoogste getal van de nummering van tabel1holes doen. In een tweede, derde, vierde query koppel ik de tabel2, tabel3, tabel4, etc. aan de tabel1.

---

Theoretisch voorbeeld:

Voor het overzicht laten we aannemen dat we alleen te maken hebben met tabel1 waarin opeenlopende ID's staan. Tabel2, tabel3 staan een aantal nummers uit die tabel1, maar niet allemaal. Hoe krijg ik nu uit tabel1 random een nummer die ook voorkomt in zowel tabel2 als tabel3? Met de volgende query krijg ik random een nummer uit tabel1:

SQL:

SELECT (RAND() * (SELECT MAX(`Tabel1ID`) FROM `Tabel1`)) AS `Tabel1ID`

Echter moet hij eigenlijk alleen de nummers zien die ook voorkomen in Tabel2 en Tabel3. Maaar mij besluipt het gevoel dat ik dan met de functie MAX() niet op het juiste spoor zit, maar neemt niet weg dat het genereren van een ID wel supersnel gaat op deze manier. En de hele tabel lezen en dan RAND() doen dat zoek ik eigenlijk ook niet. Dan gaat hij eerst alles joinen en tmp tabellen aanmaken etc.

Edit:

In het voorbeeld van Dido zie ik dat hij in beginsel gewoon geen selectie wil maken met

SQL:

SELECT rand() * max(id) as Foo from tabel1

Vervolgens selecteert hij 'vanaf' dat ID de eerstvolgende ID met:

SQL:

SELECT a.id as Bar
  FROM tabel1 a
WHERE EXISTS (SELECT b.id
                FROM tabel2 b
               WHERE b.t1_id = a.id)
  AND EXISTS (SELECT c.id
                FROM tabel3 c
               WHERE c.t1_id = a.id)
  AND a.id >= Foo
LIMIT 1

Dit geeft welliswaar niet een echte gelijke verdeling, maar een begin is er in elk geval wel.
Is er misschien toch nog een betere manier om deze twee query's in één query te krijgen?

[ Voor 11% gewijzigd door Gerwin op 04-12-2007 23:23 ]

[Gerwin]

Kan ik volgende relatief veilig doen? Op deze manier heb ik geloof ik wat ik wil hebben, ziet iemand nog dingen die wellicht anders kunnen of netter kan?

SQL:

SELECT
    Shop.ShopID AS ShopID,
    Shop.Shop AS Shop,
    Product.ProductID AS ProductID,
    Product.Product AS Product,
    Title.TitleID AS TitleID,
    Title.Title AS Title,
    Description.DescriptionID AS DescriptionID,
    Description.Description AS Description,
    Text.TextID AS TextID,
    Text.Text AS Text
FROM
    Product
    LEFT JOIN Shop ON Product.ShopID = Shop.ShopID
    LEFT JOIN Company ON Shop.CompanyID = Company.CompanyID
    
    LEFT JOIN Shop2Title ON Shop.ShopID = Shop2Title.ShopID
    LEFT JOIN Product2Title ON Product.ProductID = Product2Title.ProductID
    LEFT JOIN Title ON (
        Shop2Title.TitleID = Title.TitleID
        OR Product2Title.TitleID = Title.TitleID)
    
    LEFT JOIN Shop2Description ON Shop.ShopID = Shop2Description.ShopID
    LEFT JOIN Product2Description ON
        Product.ProductID = Product2Description.ProductID
    LEFT JOIN Description ON (
        Shop2Description.DescriptionID = Description.DescriptionID 
        OR Product2Description.DescriptionID = Description.DescriptionID)

    LEFT JOIN Shop2Text ON Shop.ShopID = Shop2Text.ShopID
    LEFT JOIN Product2Text ON Product.ProductID = Product2Text.ProductID
    LEFT JOIN Text ON (
        Shop2Text.TextID = Text.TextID 
        OR Product2Text.TextID = Text.TextID)
WHERE
    (EXISTS
        (SELECT Shop2Title.TitleID
         FROM Shop2Title
         WHERE Shop2Title.ShopID = Shop.ShopID)
    OR  (SELECT Product2Title.TitleID
         FROM Product2Title
         WHERE Product2Title.ProductID = Product.ProductID))
AND
    (EXISTS
        (SELECT Shop2Description.DescriptionID
         FROM Shop2Description
         WHERE Shop2Description.ShopID = Shop.ShopID)
    OR (SELECT Product2Description.DescriptionID
         FROM Product2Description
         WHERE Product2Description.ProductID = Product.ProductID))
AND
    (EXISTS
        (SELECT Shop2Text.TextID
         FROM Shop2Text
         WHERE Shop2Text.ShopID = Shop.ShopID)
    OR (SELECT Product2Text.TextID
         FROM Product2Text
         WHERE Product2Text.ProductID = Product.ProductID))
AND
    Product.ProductID >= (SELECT ProductID
                                 FROM ProductHoles JOIN (
                                    SELECT CEIL(RAND() * (
                                       SELECT MAX(ProductHolesID)
                                       FROM ProductHoles)) AS ProductHolesID )
                                       AS r USING (ProductHolesID))
LIMIT 1

Sorry voor de layout, maar ik wil toch nog even de EXPLAIN tabel plaatsen.
Zijn hier dingen te zien die beter kunnen of niet acceptabel zijn?

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Product	ALL	NULL	NULL	NULL	NULL	2727	Using where
1	PRIMARY	Shop	eq_ref	PRIMARY	PRIMARY	1	test.Product.ShopID	1	Using where
1	PRIMARY	Company	eq_ref	PRIMARY	PRIMARY	4	test.Shop.CompanyID	1	Using index
1	PRIMARY	Shop2Title	ref	Shop2Title	Shop2Title	4	test.Shop.ShopID	1	Using index
1	PRIMARY	Product2Title	ref	Product2Title	Product2Title	4	test.Product.ProductID	1	Using index
1	PRIMARY	Title	ALL	PRIMARY	NULL	NULL	NULL	103
1	PRIMARY	Shop2Description	ref	Shop2Description	Shop2Description	4	test.Shop.ShopID	4	Using index
1	PRIMARY	Product2Description	ref	Product2Description	Product2Description	4	test.Product.ProductID	1	Using index
1	PRIMARY	Description	ALL	PRIMARY	NULL	NULL	NULL	397
1	PRIMARY	Shop2Text	ref	Shop2Text	Shop2Text	4	test.Shop.ShopID	1	Using index
1	PRIMARY	Product2Text	ref	Product2Text	Product2Text	4	test.Product.ProductID	1	Using index
1	PRIMARY	Text	ALL	PRIMARY	NULL	NULL	NULL	3715	Using where
8	UNCACHEABLE SUBQUERY	<derived9>	system	NULL	NULL	NULL	NULL	1
8	UNCACHEABLE SUBQUERY	ProductHoles	const	PRIMARY	PRIMARY	2	const	1
9	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
10	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
7	DEPENDENT SUBQUERY	Product2Text	ref	Product2Text	Product2Text	4	test.Product.ProductID	1	Using where; Using index
6	DEPENDENT SUBQUERY	Shop2Text	ref	Shop2Text	Shop2Text	4	test.Shop.ShopID	1	Using where; Using index
5	DEPENDENT SUBQUERY	Product2Description	ref	Product2Description	Product2Description	4	test.Product.ProductID	1	Using where; Using index
4	DEPENDENT SUBQUERY	Shop2Description	ref	Shop2Description	Shop2Description	4	test.Shop.ShopID	4	Using where; Using index
3	DEPENDENT SUBQUERY	Product2Title	ref	Product2Title	Product2Title	4	test.Product.ProductID	1	Using where; Using index
2	DEPENDENT SUBQUERY	Shop2Title	ref	Shop2Title	Shop2Title	4	test.Shop.ShopID	1	Using where; Using index

Update

Hoe kan het nu dat de query af en toe traag is (tot één minuut lang) en toch maar een execution time van 0.05 seconden geeft?

Bedenk me net dat ik met bovenstaande query natuurlijk weer telkens de eerste combiniatie terugkrijg op die selectie, en er werderom de results niet gemixed worden. Wederom terug naar de tekentafel....

update:

Ik blijf aan het editen

Probleem waar ik nu tegenaanloop is nadat ik volgende methode in meerdere query gebruik.

Dido schreef op zaterdag 24 november 2007 @ 12:10:
Maar even een nieuwe, ik blijf editten

In mijn laatste query weet ik niet zeker of hij stopt na het eerste record (vanwege de limit 1 zonder sort zou het kunnen dat hij zo slim is, maar ik vertrouw niet op intelligentie van MySQL )
Als hij dat niet doet gaat het weer niet zo snel, want dan gaat hij voor ieder record in je tabel een rand()*max(id) berekenen en dat schiet niet op.

Dan kom ik toch weer uit bij een semi-applicatieve oplossing - puur vanuit performance-oogpunt:

SQL:

SELECT rand() * max(id) as Foo from tabel1

code:

MyVar = Foo

SQL:

SELECT a.id as Bar
  FROM tabel1 a
WHERE EXISTS (SELECT b.id
                FROM tabel2 b
               WHERE b.t1_id = a.id)
  AND EXISTS (SELECT c.id
                FROM tabel3 c
               WHERE c.t1_id = a.id)
  AND a.id >= MyVar
LIMIT 1

code:

MyOtherVar  = Bar

SQL:

SELECT * FROM tabel2 where t1_id = MyOtherVar;
SELECT * FROM tabel3 where t1_id = MyOtherVar;

In die laatste fase moet ik natuurlijik tabel2 en tabel3, etc. ook random hebben. Dat zou dan zoiets moeten worden:

SQL:

SELECT * FROM tabel2 where t1_id = MyOtherVar   ORDER BY RAND() LIMIT 1;;
SELECT * FROM tabel3 where t1_id = MyOtherVar  ORDER BY RAND() LIMIT 1;

Waarbij de MyOtherVar uiteraard de geldige tabel1 id is. Maar nu gaat ie weer tmp tables zwappen. Is het echt niet te voorkomen?

[ Voor 141% gewijzigd door Gerwin op 05-12-2007 15:26 ]

[Gerwin]

Na het zoeken om een mogelijkheid om toch van die RAND() af te komen heb ik volgende gemaakt. De eerste resultaten zijn echter niet al te best. Wat is sneller via Mysql of alle ID's in een Array lezen en dan een random selectie maken met array_rand(). Geloof niet dat de server dit slikt. In de test-tabel heb ik ruim 600.000 records staan en een array met 600.000 waarden zal wel beetje overkill zijn... Suggesties zijn welkom... ik ben altijd nog bezig deze 'case' te teckelen.

PHP:

$query = "SELECT ID FROM `test`";
$result = $mysql->query($query);
$iterance = 0;
$multiArray = array(); 
while ($row = $mysql->fetch_array($result)){
    $multiArray[$iterance++] = $row;
}
//print_r($multiArray);
srand((float) microtime() * 10000000);
$rand_keys = array_rand($multiArray, 2);
echo $multiArray[$rand_keys[0]][ID] . "\n";

[Gerwin]

Opnieuw heb ik een poging gewaagd voor een oplossing de input van deze kant is wat mager en in Google kom ik nu telkens mijn eigen topic tegen over dit onderwerp, maargoed.

SQL:

SELECT `Description`.`DescriptionID`, `Description`.`Description`
FROM `Description`
    LEFT JOIN `Shop2Description` ON `Description`.`DescriptionID` = `Shop2Description`.`DescriptionID`
    LEFT JOIN `Product2Description` ON `Description`.`DescriptionID` = `Product2Description`.`DescriptionID`
WHERE
    `Shop2Description`.`ShopID` =1 OR `Product2Description`.`ProductID` =1
ORDER BY RAND()
LIMIT 1 

Deze query maakt wel gebruik van RAND(), maar ik had de hoop dat alles toch snel zou gaan omdat hij enkel de rows pakt uit Description tabel die aan de WHERE conditie voldoen, dit gaat echter niet best. De query met minder dan 1000 records in alle tabellen duurt bijna 1 seconde.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Description	ALL	NULL	NULL	NULL	NULL	397	Using temporary; Using filesort
1	SIMPLE	Shop2Description	index	NULL	Shop2Description	8	NULL	412	Using index
1	SIMPLE	Product2Description	index	NULL	Product2Description	8	NULL	412	Using where; Using index

[Gerwin]

Vanochtend maar weer eens in het script gedoken, een beetje meer feedback zou overigens niet een overbodige luxe zijn... Wat ik nu heb is he volgende:

SQL:

## genereer een ProductID
$query = "
SELECT ProductID
FROM ProductHoles JOIN (
    SELECT CEIL(RAND() * (
        SELECT MAX(ProductHolesID) 
        FROM ProductHoles)) AS ProductHolesID ) AS r USING (ProductHolesID)
";
$row = $mysql->fetch_object($mysql->query($query));

## bepaal de eerste geldige ProductID
$queryProduct = "
SELECT Product.ProductID AS ProductID, Product.Product AS Product, Shop.ShopID AS ShopID
FROM
    Product LEFT JOIN Shop ON Product.ShopID = Shop.ShopID
WHERE
        (EXISTS (SELECT Shop2Title.TitleID
                    FROM Shop2Title
                    WHERE Shop2Title.ShopID = Shop.ShopID)
              OR (SELECT Product2Title.TitleID
                    FROM Product2Title
                    WHERE Product2Title.ProductID = Product.ProductID))
  AND   (EXISTS (SELECT Shop2Description.DescriptionID
                    FROM Shop2Description
                    WHERE Shop2Description.ShopID = Shop.ShopID)
              OR (SELECT Product2Description.DescriptionID
                    FROM Product2Description
                    WHERE Product2Description.ProductID = Product.ProductID))
  AND (EXISTS (SELECT Shop2Text.TextID
                    FROM Shop2Text
                    WHERE Shop2Text.ShopID = Shop.ShopID)
              OR (SELECT Product2Text.TextID
                    FROM Product2Text
                    WHERE Product2Text.ProductID = Product.ProductID))
  AND Product.ProductID >= ".$row->ProductID."
LIMIT 1
";
//echo $queryProduct;
$rowProduct = $mysql->fetch_object($mysql->query($queryProduct));
$Product = $rowProduct->Product;

// bepalen TitleID
$queryTitle = "
SELECT `Title`.`TitleID`, `Title`.`Title`
FROM `Title`,`Shop2Title`,`Product2Title`
WHERE 
     (`Shop2Title`.`ShopID` =".$rowProduct->ShopID." OR `Product2Title`.`ProductID` =".$rowProduct->ProductID.")
AND `Title`.`TitleID` = `Shop2Title`.`TitleID`
AND `Title`.`TitleID` = `Product2Title`.`TitleID`";
//echo $queryTitle;
$resultTitle = $mysql->query($queryTitle);
$iterance = 0;
$multiArrayTitle = array(); 
while ($rowTitle = $mysql->fetch_array($resultTitle)){
    $multiArrayTitle[$iterance++] = $rowTitle;
}
srand((float) microtime() * 10000000);
$rand_keys = array_rand($multiArrayTitle, 1);
$Title = $multiArrayTitle[0][Title];

// bepalen DescriptionID
$queryDescription = "
SELECT `Description`.`DescriptionID`, `Description`.`Description`
FROM `Description`,`Shop2Description`,`Product2Description`
WHERE 
     (`Shop2Description`.`ShopID` =".$rowProduct->ShopID." OR `Product2Description`.`ProductID` =".$rowProduct->ProductID.")
AND `Description`.`DescriptionID` = `Shop2Description`.`DescriptionID`
AND `Description`.`DescriptionID` = `Product2Description`.`DescriptionID`";
$resultDescription = $mysql->query($queryDescription);
$iterance = 0;
$multiArrayDescription = array(); 
while ($rowDescription = $mysql->fetch_array($resultDescription)){
    $multiArrayDescription[$iterance++] = $rowDescription;
}
srand((float) microtime() * 10000000);
$rand_keys = array_rand($multiArrayDescription, 1);
$Description = $multiArrayDescription[0][Description];

// bepalen TextID
$queryText = "
SELECT `Text`.`TextID`, `Text`.`Text`
FROM `Text`,`Shop2Text`,`Product2Text`
WHERE 
     (`Shop2Text`.`ShopID` =".$rowProduct->ShopID." OR `Product2Text`.`ProductID` =".$rowProduct->ProductID.")
AND `Text`.`TextID` = `Shop2Text`.`TextID`
AND `Text`.`TextID` = `Product2Text`.`TextID`";
$resultText = $mysql->query($queryText);
$iterance = 0;
$multiArrayText = array(); 
while ($rowText = $mysql->fetch_array($resultText)){
    $multiArrayText[$iterance++] = $rowText;
}
srand((float) microtime() * 10000000);
$rand_keys = array_rand($multiArrayText, 1);
$Text = $multiArrayText[0][Text];

Hiermee wil ik bijvoorbeeld op een frontpage 20 producten weergeven. Echter de laadtijd staat me niet aan. Hoewel toch alles snel zou moeten gaan op deze manier zou je denken. Met 20 maal deze code achter elkaar in een php functie en die uiteindelijk de 3 rijen echo'd kom ik op een pagina loading van 7 seconden. Bij 100 kom doet hij er bijna 30 seconden over. Wat is de bottkleneck, als ik de query afzonderlijk doe is alles binnen bij wijze van een duizendste seconde gereed, duizendste secode maal 100 en keer 4-5 is toch iets van 1 seconde ? Helemaal niemand die enig idee heeft of ooit met een vergelijkbaar bijltje gehakt heeft hier?

[ATS]

Waarom meet je niet gewoon wat de tijd kost in plaats van het hier te vragen? Dan zie je dat toch zo?
Dan, even zonder echt goed te kijken wat je doet, waarom run je dezelfde code 20 keer? Waarop haal je niet in één keer die 20 producten op? Waarom reseed je elke keer de random number generator, zeker omdat dat reseeden helemaal niet nodig is? Waarom kies je een random rij, maar gebruik je die keuze niet?

[ Voor 7% gewijzigd door ATS op 06-12-2007 16:47 ]

[Gerwin]

ATS schreef op donderdag 06 december 2007 @ 16:45:
Waarom meet je niet gewoon wat de tijd kost in plaats van het hier te vragen? Dan zie je dat toch zo?
Dan, even zonder echt goed te kijken wat je doet, waarom run je dezelfde code 20 keer? Waarop haal je niet in één keer die 20 producten op? Waarom reseed je elke keer de random number generator, zeker omdat dat reseeden helemaal niet nodig is? Waarom kies je een random rij, maar gebruik je die keuze niet?

Ik heb alle querys afzonderlijk gemeten en dan zijn er geen grote laadtijden zoals ik zei; laadtijd van ~0.0003 seconden per query. Echter nu ze acher elkaar gedaan worden komen er laadtijden uit die oplopen tot 30 seconden bij 100 producten. Dat terwijl je zou denken: 100 x 5 querys x 0,0003 seconden = 0.15 seconden laadtijd totaal.

De code zoveel keer achter elkaar heeft te maken met het feit waarom dit topic gestart is: random een product uit tabel1 selecteren waarvan een geldige random titel, description en text in andere tabellen bestaat. Hoe had jij random een productnummer gepakt en daat random een title, description en text aan gekoppeld dan? Dido merkte eerder op dat alles in meerdere querś moet omdat er geen feitelijke directe relaties bestaan. Je opmerking zet me in elk geval wel weer aan het denken, hoewel ik niet helemaal weet in welke richting ik moet gaan.

In de meest recente versie van php kan de reseed eruit inderdaad. Dat gaat automatisch, dank voor de aanvulling ik zal het verwijderen.

Met je vraag waarom ik niets met de keuze doe van de random rij is me niet helemaal duidelijk. Ik genereer eerst een ramdon ProductID geldig ProductID nummer. Dit doe ik uit ProductHoles mocht er voorkomen dat er een gat in de nummering zit dan word het random selecteren toch gelijkmatig verdeeld. Vervolgens bepaal in in de tweede query de eerste geldige ProductID waarvan bekend is dat Title, Description Text etc. 'exists'. (deze aktie in één query heb ik geen mogelijkheid voor gezien tot nu toe, uiteraard als iemand wel een mogelijkheid ziet is dat zeer welkom. Als ik dan dat ProductID heb dan selecteer ik random één voor één de Titel, Description, Text erbij, niet middels RAND() maar middels een array, op deze manier krijg ik in mysql geen hoge laadtijden en het zou sneller moeten werken lijkt me.

[Gerwin]

Nadat ik de ProductID random bepaald heb en random een Titel wil hebben die bij dit ProductID hoort heb ik de volgende twee mogelijkheden gevonden:

PHP:

$queryTitle = "
SELECT `Title`.`Title` AS Title
from 
(
SELECT DISTINCT(`Title`.`Title`)
FROM `Title`,`Shop2Title`,`Product2Title`
WHERE
   `Shop2Title`.`ShopID` =".$rowProduct->ShopID." 
   AND `Title`.`TitleID` = `Shop2Title`.`TitleID`
union
SELECT DISTINCT(`Title`.`Title`)
FROM `Title`,`Shop2Title`,`Product2Title`
WHERE
   `Product2Title`.`ProductID` = ".$rowProduct->ProductID." 
   AND `Title`.`TitleID` = `Product2Title`.`TitleID`
) as `Title`
ORDER BY RAND()
LIMIT 1";
$rowTitle = $mysql->fetch_object($mysql->query($queryTitle));
$Title = $rowTitle->Title;

of

PHP:

$queryTitle = "
SELECT `Title`.`Title` AS Title
from 
(
SELECT DISTINCT(`Title`.`Title`)
FROM `Title`,`Shop2Title`,`Product2Title`
WHERE
   `Shop2Title`.`ShopID` =".$rowProduct->ShopID." 
   AND `Title`.`TitleID` = `Shop2Title`.`TitleID`
union
SELECT DISTINCT(`Title`.`Title`)
FROM `Title`,`Shop2Title`,`Product2Title`
WHERE
   `Product2Title`.`ProductID` = ".$rowProduct->ProductID." 
   AND `Title`.`TitleID` = `Product2Title`.`TitleID`
) as `Title`
";
$resultTitle = $mysql->query($queryTitle);
$iterance = 0;
$multiArrayTitle = array(); 
while ($rowTitle = $mysql->fetch_array($resultTitle)){
    $multiArrayTitle[$iterance++] = $rowTitle;
}
$rand_keys = array_rand($multiArrayTitle, 1);
$Title = $multiArrayTitle[0][Title];

Welke is de beste keus? Is er nog een betere manier?

[Creepy]

Niet om het 1 of ander, maaruh, wat vind je zelf de beste keus en waarom? Je laatste paar posts komt meer neer op "ik heb dit en dit geef aub eens feedback". Het zou fijn zijn als je ook zou aangeven waarom je iets zo doet en welke je zelf de beste vindt. Als beste "snelste" betekent dan kan je dat natuurlijk makkelijk zelf nakijken.

Ik zelf zou de DB zoveel mogelijk werk laten doen, dus niet de zaken in een array inladen en er dan zelf random 1 uit kiezen maar je DB dat werk laten doen.

[ Voor 19% gewijzigd door Creepy op 07-12-2007 09:04 ]

[ATS]

Gerwin schreef op vrijdag 07 december 2007 @ 01:06:Ik heb alle querys afzonderlijk gemeten en dan zijn er geen grote laadtijden zoals ik zei; laadtijd van ~0.0003 seconden per query. Echter nu ze acher elkaar gedaan worden komen er laadtijden uit die oplopen tot 30 seconden bij 100 producten. Dat terwijl je zou denken: 100 x 5 querys x 0,0003 seconden = 0.15 seconden laadtijd totaal.

Maar heb je het ook in je PHP code gemeten? Echo gewoon eens op wat verschillende plaatsen in je PHP code de runtime, en kijk waar de tijd gebruikt wordt. Anders ga je het nooit vinden.

De code zoveel keer achter elkaar heeft te maken met het feit waarom dit topic gestart is: random een product uit tabel1 selecteren waarvan een geldige random titel, description en text in andere tabellen bestaat. Hoe had jij random een productnummer gepakt en daat random een title, description en text aan gekoppeld dan? Dido merkte eerder op dat alles in meerdere querś moet omdat er geen feitelijke directe relaties bestaan. Je opmerking zet me in elk geval wel weer aan het denken, hoewel ik niet helemaal weet in welke richting ik moet gaan.

Dat ging erover dat het niet zou gaan lukken om tegelijk een random, bijpassende row uit table 2 en 3 te halen. Dát moet je in aparte queries doen. Dat wil niet zeggen dat je eerste 20 random records uit table 1 in 20x moet ophalen. Dat is natuurlijk onzinnig.

Met je vraag waarom ik niets met de keuze doe van de random rij is me niet helemaal duidelijk.

Kijk nu eens kritisch naar 75-77 en 93-95. Wat doe je daar nu eigenlijk? Je kiest een random element uit je array, en dan... gebruik je element 0.