[MySQL5] ORDER BY RAND()

Ich hab folgende Abfrage mit einem sub select:

| SELECT
| u.name,
| l.guid,
| h.score
| FROM
| u, l, h
| WHERE
| h.fk_id_u=u.id
| AND
| h.fk_id_l=l.id
| AND
| l.id=(SELECT
| id
| FROM
| l
| ORDER BY RAND() LIMIT 1);
|
| ORDER BY ...
| LIMIT ...

Das Ziel dieser Abfrage ist folgendes:

Es gibt je eine Tabelle USER (u), LEVEL (l) und HIGHSCORE (h). In
HIGHSCORE wird über FKs die Beziehung zw. USER und LEVEL hergestellt
und der Score-Wert zugeordnet. Ich möchte nun per Zufall einen Level
auswählen und von diesem die X besten anzeigen.

Im Moment kann 'l.id'(int) die Werte 1 bis 3 annehmen, für jede dieser
IDs liefert das obige Konstrukt mind. einen Datensatz. Trotzdem bekomme
ich mit diese Abfrage sehr häufig ein leeres Ergebnis. Setze ich statt
dem sub select dort 1 bis 3 drei ein, kommen meine gewünschten
Datensätze. Genauso liefert das sub select allein immer richtig 1 bis 3.

Unter [0] steht folgender Hinweis:

"ORDER BY RAND() combined with LIMIT is useful for selecting
a random sample from a set of rows:

[...]

Note that RAND() in a WHERE clause is re-evaluated every time
the WHERE is executed."

Ist das die Ursache, weil sich 'l.id' Zeile für Zeile ändert?
Kann ich das trotzdem irgendwie in _einer_ Abfrage lösen?

Danke
Lars

[0] http://dev.mysql.com/doc/refman/5.0/en/mathematical-function s.html

Danke
Lars

Re: [MySQL5] ORDER BY RAND()

On Thu, 08 Feb 2007 10:38:39 +0100, Lars Uhlmann wrote:

> Ich hab folgende Abfrage mit einem sub select:
>
> | SELECT
> | u.name,
> | l.guid,
> | h.score
> | FROM
> | u, l, h
> | WHERE
> | h.fk_id_u=u.id
> | AND
> | h.fk_id_l=l.id
> | AND
> | l.id=(SELECT
> | id
> | FROM
> | l
> | ORDER BY RAND() LIMIT 1);
> |
> | ORDER BY ...
> | LIMIT ...
>
> Das Ziel dieser Abfrage ist folgendes:
>
> Es gibt je eine Tabelle USER (u), LEVEL (l) und HIGHSCORE (h). In
> HIGHSCORE wird über FKs die Beziehung zw. USER und LEVEL hergestellt
> und der Score-Wert zugeordnet. Ich möchte nun per Zufall einen Level
> auswählen und von diesem die X besten anzeigen.
>
> Im Moment kann 'l.id'(int) die Werte 1 bis 3 annehmen, für jede dieser
> IDs liefert das obige Konstrukt mind. einen Datensatz. Trotzdem bekomme
> ich mit diese Abfrage sehr häufig ein leeres Ergebnis. Setze ich statt
> dem sub select dort 1 bis 3 drei ein, kommen meine gewünschten
> Datensätze. Genauso liefert das sub select allein immer richtig 1 bis 3.
>
> Unter [0] steht folgender Hinweis:
>
> "ORDER BY RAND() combined with LIMIT is useful for selecting
> a random sample from a set of rows:
>
> [...]
>
> Note that RAND() in a WHERE clause is re-evaluated every time
> the WHERE is executed."
>
> Ist das die Ursache, weil sich 'l.id' Zeile für Zeile ändert?

Ja. Wie EXPLAIN dir mitteilen würde behandelt MySQL das als UNCACHEABLE
SUBQUERY.

> Kann ich das trotzdem irgendwie in _einer_ Abfrage lösen?

Ja.

SELECT
u.name,
l.guid,
h.score
FROM
(SELECT id, name FROM u ORDER BY RAND() LIMIT 1) u
INNER JOIN
h ON (h.fk_id_u = u.id)
INNER JOIN
l ON (l.id = h.fk_id_l)
ORDER BY
...
LIMIT
...

Björn

Re: [MySQL5] ORDER BY RAND()

Björn Steinbrink schrieb:

>>Ich hab folgende Abfrage mit einem sub select:
>>
>>| SELECT
>>| u.name,
>>| l.guid,
>>| h.score
>>| FROM
>>| u, l, h
>>| WHERE
>>| h.fk_id_u=u.id
>>| AND
>>| h.fk_id_l=l.id
>>| AND
>>| l.id=(SELECT
>>| id
>>| FROM
>>| l
>>| ORDER BY RAND() LIMIT 1);
>>|
>>| ORDER BY ...
>>| LIMIT ...
>>
>>Trotzdem bekomme ich mit diese Abfrage sehr häufig ein leeres
>>Ergebnis.
>>
>>[...]
>>
>> Note that RAND() in a WHERE clause is re-evaluated every time
>> the WHERE is executed."
>>
>>Ist das die Ursache, weil sich 'l.id' Zeile für Zeile ändert?
>
> Ja. Wie EXPLAIN dir mitteilen würde behandelt MySQL das als UNCACHEABLE
> SUBQUERY.

Stimmt, es existiert EXPLAIN...

>>Kann ich das trotzdem irgendwie in _einer_ Abfrage lösen?
>
> Ja.
>
> SELECT
> u.name,
> l.guid,
> h.score
> FROM
> (SELECT id, name FROM u ORDER BY RAND() LIMIT 1) u
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> INNER JOIN
> h ON (h.fk_id_u = u.id)
> INNER JOIN
> l ON (l.id = h.fk_id_l)

Das funktioniert leider nicht (Empty set). Ich verstehe auch nicht, was
in der markierten FROM-Zeile abläuft. Müßte da nicht die Tabelle 'l'
(LEVEL) verwendet werden (ich möchte ja einen zufällig ausgewählten
level)?

Lars

Re: [MySQL5] ORDER BY RAND()

On Thu, 08 Feb 2007 13:10:54 +0100, Lars Uhlmann wrote:

> Björn Steinbrink schrieb:
>
>>>Ich hab folgende Abfrage mit einem sub select:
>>>
>>>| SELECT
>>>| u.name,
>>>| l.guid,
>>>| h.score
>>>| FROM
>>>| u, l, h
>>>| WHERE
>>>| h.fk_id_u=u.id
>>>| AND
>>>| h.fk_id_l=l.id
>>>| AND
>>>| l.id=(SELECT
>>>| id
>>>| FROM
>>>| l
>>>| ORDER BY RAND() LIMIT 1);
>>>|
>>>| ORDER BY ...
>>>| LIMIT ...
>>>
> >>Trotzdem bekomme ich mit diese Abfrage sehr häufig ein leeres
> >>Ergebnis.
>>>
>>>[...]
>>>
>>> Note that RAND() in a WHERE clause is re-evaluated every time
>>> the WHERE is executed."
>>>
>>>Ist das die Ursache, weil sich 'l.id' Zeile für Zeile ändert?
>>
>> Ja. Wie EXPLAIN dir mitteilen würde behandelt MySQL das als UNCACHEABLE
>> SUBQUERY.
>
> Stimmt, es existiert EXPLAIN...
>
>>>Kann ich das trotzdem irgendwie in _einer_ Abfrage lösen?
>>
>> Ja.
>>
>> SELECT
>> u.name,
>> l.guid,
>> h.score
>> FROM
>> (SELECT id, name FROM u ORDER BY RAND() LIMIT 1) u
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>> INNER JOIN
>> h ON (h.fk_id_u = u.id)
>> INNER JOIN
>> l ON (l.id = h.fk_id_l)
>
> Das funktioniert leider nicht (Empty set). Ich verstehe auch nicht, was
> in der markierten FROM-Zeile abläuft. Müßte da nicht die Tabelle 'l'
> (LEVEL) verwendet werden (ich möchte ja einen zufällig ausgewählten
> level)?

Argh, natürlich. Mein Gehirn scheint zufällige User zu bevorzugen, da
hätte ich mal einfach kopieren/einfügen spielen sollen :(

SELECT
u.name,
l.guid,
h.score
FROM
(SELECT id, guid FROM l ORDER BY RAND() LIMIT 1) l
INNER JOIN
h ON (h.fk_id_l = l.id)
INNER JOIN
u ON (u.id = h.fk_id_u)

Das kann natürlich immer noch ein leeres Ergebnis liefern, falls noch
keine Einträge für das zufällig gewählte Level in h existieren.

Björn

Re: [MySQL5] ORDER BY RAND()

Björn Steinbrink schrieb:

>>>SELECT
>>> u.name,
>>> l.guid,
>>> h.score
>>>FROM
>>> (SELECT id, name FROM u ORDER BY RAND() LIMIT 1) u
>>
>> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>>
>>>INNER JOIN
>>> h ON (h.fk_id_u = u.id)
>>>INNER JOIN
>>> l ON (l.id = h.fk_id_l)
>>
>>Das funktioniert leider nicht (Empty set). Ich verstehe auch nicht, was
>>in der markierten FROM-Zeile abläuft. Müßte da nicht die Tabelle 'l'
>>(LEVEL) verwendet werden (ich möchte ja einen zufällig ausgewählten
>>level)?
>
>
> Argh, natürlich. Mein Gehirn scheint zufällige User zu bevorzugen, da
> hätte ich mal einfach kopieren/einfügen spielen sollen :(

Hatte ich ähnlich vermutet, war mir aber nicht sicher. :)
Die query sieht jetzt so aus:

SELECT
u.name,
l.guid,
h.score
FROM
(
SELECT
id,
guid
FROM
LEVEL
ORDER BY RAND()
LIMIT 1
) AS l
INNER JOIN
HIGHSCORE AS h ON (h.fk_id_LEVEL = l.id)
INNER JOIN
USER AS u ON (u.id = h.fk_id_USER)
ORDER BY h.score DESC
LIMIT 10

und funktioniert hervorprächtig.

Lars