0

I am trying to create a Flexible Search that retrieves 10,000 random rows out of 3M. Tried with different syntax like LIMIT, but i cannot make it work, I need to add this query to a Groovy Script. Thought about creating a random number and retrieve 10k from all the rows, but performance will be heavy.

Any suggestions?

Query Example:

SELECT {pk} FROM {Order as O} "Condition to get random registries from it".

Thank you!!

sri harsha
  • 676
  • 6
  • 16
Mauricio Jimenez
  • 11
  • 1
  • 1
    Big topic, undoubtly duplicates exist if you search on this or http://dba.stackexchange.com/. [this](http://mysql.rjweb.org/doc.php/random) should be a good start. – danblack Jan 11 '19 at 05:44
  • Possible duplicate of [MySQL select 10 random rows from 600K rows fast](https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) – cfrick Jan 11 '19 at 07:19

3 Answers3

0

You can't do that directly with Flexible search.

One solution would be to query the IDs of your Orders, then get random ID's from your list using Java (you can easily find how to do it on StackOverflow).

Once you have random IDs you can build an other Flexible search to query the full rows.

alain.janinm
  • 19,951
  • 10
  • 65
  • 112
0
SELECT {o.PK} FROM {Order as o} ORDER BY RAND()

Pay attention that RAND() function is DB engine specific.

Ihar Reznichenka
  • 81
  • 5
0
... order by REVERSE(concat('', {gvp:pk}))

that's was enough for me to shuffle gvps not in a sequential order

Yura
  • 1,733
  • 1
  • 20
  • 19