title: Optimize SQL statement for android sqlite
body: <p>I'm developing an application that tracks the user's current position and stores it into a SQLite database. Everything works fine, but now I have the problem when querying the database for a track with more than 1000 records it takes nearly 1.5 minutes. On my Desktop it just takes 1 second.</p>
  
  <p>I know it's a query with many subselects but I wasn't able to get the right result another way. In my opinion this belongs to the aggregate functions like avg() and sum().</p>
  
  <p>Here's my query:</p>
  
  <pre><code>Cursor c = readableDB
      .rawQuery(
              "SELECT DISTINCT t._id , title , strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime') as date , description, "
              + "round((SELECT sum(distToPrev)/1000 FROM positions p WHERE p.trackid=t._id) , 2) as distance , " 
              + "(SELECT count(latitude) FROM positions p WHERE p.trackid=t._id) as waypoints, "
              + "(SELECT (avg(speed)*3.6) FROM positions p WHERE p.trackid=t._id) as avgspeed, "
              + "(SELECT (max(speed)*3.6) FROM positions p WHERE p.trackid=t._id) as maxspeed, "
              + "(SELECT sum(altitudeUP) FROM positions p WHERE p.trackid=t._id) as climb , "
              + "(SELECT avg(heartbeat) FROM heartbeats h WHERE h.trackid=t._id) as avgheartbeat , "
              + "(SELECT max(heartbeat) FROM heartbeats h WHERE h.trackid=t._id) as maxheartbeat , "
              + "(SELECT avg(cadence) FROM heartbeats h WHERE h.trackid=t._id) as avgcadence "
              + "FROM tracks t LEFT JOIN heartbeats h ON t._id = h.trackid WHERE t._id = ?",
              new String[]{String.valueOf(trackId)}); 
      c.moveToFirst();
  </code></pre>
  
  <p>How can I optimize this query? I tried it already this way, but then the result was wrong and it took the same amount of time.</p>
  
  <pre><code>SELECT t._id , title , strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime') as date , description,
  sum(distToPrev)/1000 as distance ,
  count(latitude) as waypoints, 
  (avg(speed)*3.6) as avgspeed, 
  (max(speed)*3.6) as maxspeed, 
  sum(altitudeUP) as climb , 
  avg(heartbeat)  as avgheartbeat ,
  max(heartbeat)  as maxheartbeat ,
  avg(cadence) as avgcadence 
  FROM tracks t 
  LEFT JOIN heartbeats h ON t._id = h.trackid 
  INNER JOIN positions p ON t._id = p.trackid 
       WHERE t._id = ?
  </code></pre>
  
  <p>Since 2 hours I'm looking for a solution and I don't know what I'm doing wrong. Maybe I have to take a break.</p>
  
  <p>EDIT: </p>
  
  <p>Here my create statements:</p>
  
  <pre><code>CREATE TABLE heartbeats(_id INTEGER PRIMARY KEY AUTOINCREMENT, trackid INTEGER NOT NULL, heartbeat INTEGER NOT NULL, cadence INTEGER, timestamp TIMESTAMP);
  
  
  CREATE TABLE positions(_id INTEGER PRIMARY KEY AUTOINCREMENT, trackid INTEGER NOT NULL, longitude REAL NOT NULL, latitude REAL NOT NULL, altitude REAL, altitudeUP REAL, speed REAL, accuracy REAL, distToPrev REAL, timestamp TIMESTAMP);
  
  
  CREATE TABLE tracks(_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT, starttime DATETIME NOT NULL, endtime DATETIME);
  </code></pre>