2
$\begingroup$

I'd like to sample rows from a table uniformly over time, here is the naive way to do this:

SELECT * FROM table ORDER BY RAND() LIMIT 10000

Unfortunately, executing this query causes the table to first get sorted, which can take very very very long if it is large, and the query freezes. There are other ways for dealing with random sampling in sql, but they are really complicated. Is there any shortcut to doing this with a DatabaseLink` command?

Code to get started

Here's how to I set up my connection:

<< DatabaseLink`
DatabaseExplorer[] (* wizard to create the connection "database_name"*)
conn = OpenSQLConnection["database_name"];

For the query DatabaseLink uses the function SQLSelect, which has these options:

enter image description here

Update:

Here are the table structure details:

  • Generic mysql db with single table (381 Gb)
  • Uses InnoDB storage engine
  • All columns have btree indices
  • The table has a primary key and 589 million rows
  • The column I'm random sampling from is 'created_at' of type datetime
  • The table's schema look like this:

    CREATE TABLE photos ( id int(10) unsigned NOT NULL AUTO_INCREMENT, tweet_id varchar(255) DEFAULT NULL, created_at datetime NOT NULL, hashtags varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, text varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, source varchar(255) DEFAULT NULL, image_hash text, PRIMARY KEY (id), UNIQUE KEY photos_tweet_id (tweet_id), KEY photos_date (object_date) ) ENGINE=InnoDB AUTO_INCREMENT=589069903 DEFAULT CHARSET=latin1

$\endgroup$

closed as off-topic by MarcoB, m_goldberg, user9660, dr.blochwave, Jens May 11 '16 at 15:58

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "The question is out of scope for this site. The answer to this question requires either advice from Wolfram support or the services of a professional consultant." – MarcoB, m_goldberg, Community, dr.blochwave, Jens
If this question can be reworded to fit the rules in the help center, please edit the question.

  • $\begingroup$ Wouldn't this question be more about SQL than Mathematica though? $\endgroup$ – MarcoB May 8 '16 at 0:04
  • $\begingroup$ Lol, I'd tend to agree, but there are no good SQL solutions. Maybe Mathematica can circumvent this? $\endgroup$ – M.R. May 8 '16 at 0:06
  • 1
    $\begingroup$ How is table structured? Does it have a primary key? If so what are the column(s) of the primary key and their data type? What database product are you using? $\endgroup$ – Edmund May 8 '16 at 13:07
  • $\begingroup$ @Edmund I'll add those details now. $\endgroup$ – M.R. May 8 '16 at 22:46
  • 2
    $\begingroup$ One does not necessarily have to do a simple random sample. Assuming that there are no cyclic patterns in the database, you might consider selecting a random record in the first k records and then sample every k-th record. (So it's systematic after the random start.) There are lots of alternatives with desirable sampling properties that don't require knowing the complete population ahead of time. Details on those methods would be better addressed at Cross Validated. $\endgroup$ – JimB May 9 '16 at 1:11