I am working on optimizing query. My index is not working properly, due that my query is taking 6 to 10 minutes to respond. I need to reduce it to below 30 seconds.
Table 1: Snapshots
Columns: id , type, instance, location,x, y, z, timestamp. Where id is primary key
index 1 = (type,instance, timestamp)
index 2 = location
Table 2: SnapshotMeta
Columns: type, instance, classifier where all the columns are composite primary key
Index : (classifier, type, instance)
Table one having 100 M records and Table 2 having 5 million rows.
Combination of type/instance/timestamp/x makes a unique record. Everytime, I get a set of values for x (per type/instance). I store it using timestamp to know what were the values at every change.
I need to get unique location based on classifier,timestamp values I passed.
SELECT DISTINCT mt.location
from ( SELECT t1.location, MAX(t1.timestamp) AS maxtimestamp ,
col.instance, col.type from SnapshotMeta col
inner join Snapshots t1 on col.classifier in ( My classifiers)
AND col.type = t1.type AND col.instance=t1.instance
AND t1.timestamp <= MYTIMESTAMP
group by t1.instance) t
inner join Snapshots mt on mt.type = t.type and mt.instance = t.instance
AND mt.timestamp = t.maxtimestamp
ORDER BY t.location;
Above query taking 5 to 10 minutes to respond.
select distinct location
from (select max(timestamp) mt , type, instance
where timestamp <= MYTIMESTAMP
AND type in
(select distinct type from SnapshotMeta where classifier in (“rural”))
group by type, instance) t inner join Snapshots t1 on t.type=t1.type
AND t.instance=t1.instance AND t1.timestamp=t.mt;
Above query taking 2 to 4 minutes to respond.
In both query index not working on derived table. Is there any other way to simplify my query to works faster.