sql - MySQL query index & performance improvements -

i have created application track progress in league of legends me , friends. purpose, collect information current rank several times day mysql database. fetch results , show them in graph, use following query / queries:

select      lol_summoner.name name, grid.series + ? timestamp,      avg(nullif(lol.points, 0)) points      series_tmp grid join      lol on lol.timestamp >= grid.series , lol.timestamp < grid.series + ? join      lol_summoner on lol.summoner = lol_summoner.id group     lol_summoner.name, grid.series order     name, timestamp asc  select      lol_summoner.name name, grid.series + ? timestamp,      avg(nullif(lol.points, 0)) points      series_tmp grid join      lol on lol.timestamp >= grid.series , lol.timestamp < grid.series + ? join      lol_summoner on lol.summoner = lol_summoner.id      lol_summoner.name in (". str_repeat('?, ', count($names) - 1) ."?) group     lol_summoner.name, grid.series order     name, timestamp asc 

the first query used in case want retrieve players saved in database. grid table temporary table generated timestamps in specific interval retrive information in chunks of interval. 2 variable in query interval. second query used if want retrieve information specific players only.

the grid table produces following stored procedure called 3 parameters (n_first - first timestamp, n_last - last timestamp, n_increments - increments between 2 timestamps):

begin     -- create tmp table     drop temporary table if exists series_tmp;     create temporary table series_tmp (         series bigint     ) engine = memory;      while n_first <= n_last         -- insert in tmp table         insert series_tmp (series) values (n_first);          -- increment value 1         set n_first = n_first + n_increment;      end while; end 

the query works , finishes in reasonable time (~10 seconds) thankful improve query either rewriting or adding additional indexes database.

/edit:

after review of @rick james answer, modified queries follows:

select lol_summoner.name name, (lol.timestamp div :range) * :range + :half_range timestamp, avg(nullif(lol.points, 0)) points   lol     join lol_summoner on lol.summoner = lol_summoner.id   group lol_summoner.name, lol.timestamp div :range   order name, timestamp asc  select lol_summoner.name name, (lol.timestamp div :range) * :range + :half_range timestamp, avg(nullif(lol.points, 0)) points   lol     join lol_summoner on lol.summoner = lol_summoner.id   lol_summoner.name in (<names>)   group lol_summoner.name, lol.timestamp div " . $steps . "   order name, timestamp asc 

this improves query execution time margin (finished way under 1s).

problem 1 , solution

you need series of integers between 2 values? , differ 1? or larger value?

first, create permanent table of numbers 0 large enough value:

create table num10 ( n int ); insert num10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table nums ( n int, primary key(n))     select a.n*1000 + b.n*100 + c.n*10 + d.n         num10         join num10 b  -- note "cross join"         join num10 c         join num10 d; 

now nums has 0..9999. (make bigger if might need more.)

to sequence of consecutive numbers 123 through 234:

 select 123 + n nums n < 234-123+1; 

to sequence of consecutive numbers 12345 through 23456, in steps of 15:

 select 12345 + 15*n nums n < (23456-12345+1)/15; 

join select 1 of instead of series_tmp.

barring other issue, should speed things up.

problem 2

you grouping by series, ordering timestamp. related, might 'right' answer. think it.

problem 3

you seem building "buckets" (called "series"?) "timestamps". correct? if so, let's work backwards -- turn "timestamp" "bucket" number:

bucket_number = (timestamp - start) / bucket_size 

by doing throughout, can avoid 'problem 1' , eliminate solution it. is, reformulate entire queries in terms of buckets.