title: Proper way to speedup a specific sqlite query
body: <p>I am working on a simulation.</p>
  
  <p>In this simulation the agents moves and the movement, with coordinates and time step, is recorded in a sqlite database.</p>
  
  <p>When the simulation is completed, I need to display the position of the agents at a certain time. For this I use a query like</p>
  
  <pre><code>SELECT x,y,z,t from movement where t &lt;= ?1 and id = ?2;
  </code></pre>
  
  <p>and I take only the record with <code>max(t)</code>. This works fine but it's very slow.</p>
  
  <p>I need to speedup it, and I thought I could change the structure of the database. But I cannot insert a record of the position for each agent, because the db will grow too much.</p>
  
  <p>Same for adding a record of the "last valid position" of the agents at each time step.</p>
  
  <p>So, what are my other options?</p>
  
  <p>Thanks</p>
  
  <p>UPDATE:
  For the matter of the problem, the <code>Movements</code> table has just a bunch of fields for the position (x,y INTEGERS) time (t INTEGER) and id of the agent (Integer). 
  Number of records: timesteps is up to 50.000, agents can be up to 10.000 and movement can happen as frequent as 1/10 time steps. So in a big simulation we could have around 5.000 movements for 10.000 agents = 5M records for movements. </p>