sql - MySQL 5.6: Complex Query with Grouped Parameters -

i'm far mysql expert, , i'm struggling relatively complicated query.

i have 2 tables:

a data table columns follows:

`location` bigint(20) unsigned not null, `source` bigint(20) unsigned not null, `param` bigint(20) unsigned not null, `type` bigint(20) unsigned not null, `inittime` datetime not null default '0000-00-00 00:00:00', `validtime` datetime not null default '0000-00-00 00:00:00', `value` double default null 

a location group table columns follows:

`group` bigint(20) unsigned not null, `location` bigint(20) unsigned not null, 

the data table stores data of interest, each 'value' valid particular 'validtime'. however, data in table comes calculation run periodically. initialisation time @ calculation run stored in 'inittime' field. given calculation particular inittime may result in, 10 values being output valid times (a - j). more recent calculation, more recent inittime, may result in 10 values being output valid times (b - k). there therefore overlap in available values. want result set of values , validtimes recent inittime (i.e. max(inittime)).

i can determine recent inittime using following query:

select max(inittime) data   location = 100060 ,   source = 10 ,   param = 1 ,   type = 1; 

this takes 0.072 secs execute.

however, using sub-query retrieve data data table results in execution time of 45 seconds (it's pretty huge table, not super ridiculous).

sub-query:

select location, validtime, value data data source = 10     , location in (select location location group group = 3)     , inittime = (select max(data2.inittime) data data2 data.location = data2.location , data.source = data2.source , data.param = data2.param , data.type = data2.type) order location, validtime asc; 

(snipped validtime qualifiers brevity)

i know there's optimisation here, i'm not sure start. instead, created stored procedure perform max(inittime) query, because max(inittime) determined combo of location, source, param , type, need pass in locations comprise particular group. implemented cursors-based stored procedure before realising there must easier way.

putting aside question of optimisation via indices, how efficiently perform query on data table using recent inittime given location group, source, param , type?

thanks in advance!

mysql can poor job optimizing in subquery (sometimes). also, indexes might able help. so, write query as:

select d.location, d.validtime, d.value data d d.source = 10 ,       exists (select 1 locationgroup lg d.location = lg.location , lg.group = 3) ,       d.inittime = (select max(d2.inittime)                     data d2                     d.location = d2.location ,                           d.source = d2.source ,                           d.param = d2.param ,                           d.type = d2.type                    ) order d.location, d.validtime asc; 

for query, want indexes on data(location, source, param, type, inittime) , locationgroup(location, group), , data(source, location, validtime).