Getting data sample to plot

Question

I have a table on mysql with lots and lots of data (like >10000), and it seems pretty slow to load all the data to Java and then plot a graphic. I would like to get just a few points to plot a graphic, but I have no idea how. Like, getting data every 10 or 100 rows. Any idea?

You could easily plot all of it if you queried for it in chunks. Or you you query for a LIMIT of rows. — duffymo, Sep 03 '12 at 12:54

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

If you have an auto increment field you can simply select every 10 or 100 rows using remainder (mod):

SELECT actor_id, last_name FROM actor WHERE MOD(actor_id, 10) = 0;

You can also give each row a row number and limit your result on that:

SELECT actor_id, last_name, row_number FROM
(SELECT actor_id, last_name, @currentRow := @currentRow + 1 AS row_number FROM
actor JOIN (SELECT @currentRow := 0) Row) Data
WHERE MOD(row_number, 10) = 0;

The cool row number technique is by Daniel Vassallo. With MySQL, how can I generate a column containing the record index in a table?

Good luck :)

edited May 23 '17 at 12:12

Community

1
1

answered Sep 03 '12 at 14:05

RollingCog

294
1
5

Both answers helped me a lot, I'll accept yours because the rownum is a nice workaround that I didn't know, thanks! – Montolide Sep 11 '12 at 14:28

score 1 · Answer 2 · answered Sep 03 '12 at 14:49

Or you can divide your whole interval min(x) - max(x) to N intervals and get only one average point per interval. For instance (10 intervals here) something like this:

select round((x-@minx)/@step),avg(y) from PLOT_DATA,
(select @minx:=min(x),
        @maxx:=max(x),
        @intervals:=10, /*separated intervals count*/
        @step:=(@maxx-@minx)/@intervals
  from PLOT_DATA) t

group by round((x-@minx)/@step)
order by round((x-@minx)/@step)

Getting data sample to plot

2 Answers2