'SQLITE select random N rows

I've got two tables which both have hundreds of millions of rows.

One is PAPER. Each row is unique with a column called "paper_id" as its key. The other is PFOS. Each row has two columns, "paper_id" and "field_id".

One paepr may belong to several fields.

I need to select N rows in each group grouped by field_id in PFOS then get papers in PAPER by selected paper_id.

This is my sql:

select paper_id in PFOS where field_id in/= xxx order by random limit N

Questions

  1. How could I make it faster?
  2. When I use LIMIT(), the rows I got are less than N.Did I make a mistake in sql?

PAPER

paper_id*,title...

PFOS

paper_id,field_id

I would apprecaite it if I got you suggestions.

sqlsqliteselectrandomlimit


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions