Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #16654

Re: Keeping search results random, but same in MySQL

Show all headers | View raw

Geoff Muldoon wrote:

> On Tue, 15 Mar 2016 18:58:41 +0100, Thomas 'PointedEars' Lahn wrote:
>> Geoff Muldoon wrote:
>>> Abandon all hope.
>>> 
>>> Databases do not do "random but fixed" ordering.  They either order
>>> ascending/descending according to some stipulated attribute, or do not
>>> order at all.
>> 
>> First of all, it depends on whether there are indexes on the table.
> 
> Irrelevant.

No, apparently InnoDb tables, as opposed to MyISAM tables, are sorted 
according to the last defined PRIMARY KEY, INDEX or UNIQUE index in MySQL 
5.6.25-3 on Debian GNU/Linux at least.  That is, the table records appear to 
be rewritten in ascending index order when the index is defined, as the 
order of the rows is not in the previous insertion order even after the 
relevant index has been deleted.
 
>> Second, MySQL’s ORDER BY supports expressions.
> 
> It certainly does, but what does that have to do with "fixed randomness"?

You can use RAND(…) in the expression. see also Lew Pitcher’s follow-up 
<news:0gXFy.9304$ln5.73@fx39.iad>.

>>> If you don't set an ORDER BY you will have no control over the
>>> randomness of the return data provided from different executions of the
>>> same query.
>> Incorrect.
> 
> Say why?

I misread.  You are probably correct there as I cannot think of a way other 
than “ORDER BY … RAND(…) …” to achieve "fixed randomness".

-- 
PointedEars
Zend Certified PHP Engineer 
<http://www.zend.com/en/yellow-pages/ZEND024953> | Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Keeping search results random, but same in MySQL bit-naughty@hotmail.com - 2016-03-14 22:29 -0700
  Re: Keeping search results random, but same in MySQL Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2016-03-15 17:16 +1100
    Re: Keeping search results random, but same in MySQL Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-15 18:58 +0100
      Re: Keeping search results random, but same in MySQL Jerry Stuckle <jstucklex@attglobal.net> - 2016-03-15 14:40 -0400
      Re: Keeping search results random, but same in MySQL Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2016-03-16 13:56 +1100
        Re: Keeping search results random, but same in MySQL Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-16 19:56 +0100
  Re: Keeping search results random, but same in MySQL gordonb.paboe@burditt.org (Gordon Burditt) - 2016-03-15 01:19 -0500
  Re: Keeping search results random, but same in MySQL Arno Welzel <usenet@arnowelzel.de> - 2016-03-15 09:02 +0100
  Re: Keeping search results random, but same in MySQL Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2016-03-15 13:03 -0400
    Re: Keeping search results random, but same in MySQL bit-naughty@hotmail.com - 2016-03-16 10:26 -0700

csiph-web