Select a Portion of Vast Data Over Time with MySQL

Question

I have hundreds of thousands of price points spanning 40 years plus. I would like to construct a query that will only return 3000 total data points, with the last 500 being the most recent data points, and the other 2500 being just a sample of the rest of the data, evenly distributed.

Is it possible to do this in one query? How would I select just a sample of the large amount of data? This is a small example of what I mean for getting just a sample of the other 2500 data points:

1
2
3    
4
5
6
7
8
9
10

And I want to return something like this:

1
5
10

Here's the query for the last 500:

SELECT * FROM price ORDER BY time_for DESC LIMIT 500

I'm not sure how to go about getting the sample data from the other data points.

Answer 1

Try this:

(SELECT * FROM price ORDER BY time_for DESC LIMIT 500)
UNION ALL
(SELECT * FROM price WHERE time_for < (SELECT time_for FROM price ORDER BY time_for LIMIT 500, 1) ORDER BY rand() LIMIT 2500)
ORDER BY time_for

Note: It's probably going to be slow. How big is this table?

It might be faster to only get the primary ID from all these rows, then join it to the original in a secondary query once it's narrowed down. This is because ORDER BY rand() LIMIT has to sort the entire table. If the table is large this can take a LONG time, and a lot of disk space. Retrieving only the ID reduces the necessary disk space.