title: How to speed up a 4-way SQLITE inner join that is slow despite covering indexes on all tables
body: <p>I have 4 tables that are taking a long time to join in SQLITE (version 3.31.1). Here's a simplified view of the 5 tables involved (the first one "Objects" is not used in the join directly):</p>
  
  <pre><code>Table Objects:
      Objects.Id
  
  Table Dates:  # Each day has 2 different values of date, some tables refer to one, some to the other
      Dates.Date_cal1  # Calendar 1
      Dates.Date_cal2  # Calendar 2
  
  Table A:
      A.Object_Id
      A.Date_c1
      A.Value_A
  
  Table B:
      B.Object_Id
      B.Date_c1
      B.Value_B
  
  Table C:
      C.Object_Id
      C.Date_c2
      C.Value_C
  </code></pre>
  
  <p>The (Object_id,Date) pair form a primary key for each of A/B/C. The query I am running is like this:</p>
  
  <pre><code>SELECT Dates.Date_cal1, A.Value_A, B.Value_B, C.Value_C
      FROM Dates
      INNER JOIN A ON A.Date_c1 = Dates.Date_cal1
      INNER JOIN B ON B.Date_c1 = Dates.Date_cal1 AND B.Object_Id = A.Object_Id
      INNER JOIN C ON C.Date_c2 = Dates.Date_cal2 AND C.Object_Id = A.Object_Id
      WHERE A.Object_Id=14
  ORDER BY Dates.Date_cal1 ASC;
  </code></pre>
  
  <p>The query takes about 1-2 minutes to run. Some notes:</p>
  
  <ol>
  <li>I know some of this looks silly (eg. A and B could be merged as shown here). Just assume that the real case has a reason for all this.</li>
  <li><strong>I have covering indexes for the 4 tables:  Dates has both (Date_cal1,Date_Cal2) and (Date_cal2,Date_cal1), and A and B and C each have one ordered by (Object_Id,Date_c*,Value_*) where * is 1-or-2 and A-or-B-or-C as appropriate).</strong></li>
  <li>In terms of size, Dates has 360 rows, A and B have 3.6 million, and C has 100 million (C is bigger because "cal2" is a finer-grained calendar - you can think of it as cal1 is once a month, cal2 is once a day).</li>
  <li>The data is quite regular, meaning that each of 10k object_ids uses an approximately equal number of rows in A/B/C.</li>
  </ol>
  
  <p>When I run EXPLAIN QUERY PLAN for the query above, I get:</p>
  
  <pre><code>QUERY PLAN
  |--SEARCH TABLE B USING COVERING INDEX B_index (Object_Id=?)
  |--SEARCH TABLE A USING PRIMARY KEY (Date_c1=? AND Object_Id=?)
  |--SEARCH TABLE Dates USING PRIMARY KEY (Date_cal1=?)
  |--SEARCH TABLE C USING PRIMARY KEY (Date_c2=? AND Object_Id=?)
  `--USE TEMP B-TREE FOR ORDER BY
  </code></pre>
  
  <p>Note that only the first part mentions leveraging the covering index. I'm not sure if 1 minute is reasonable, but I was hoping that full use of the covering indexes would mean that the date and object_id filtering for A/B/C would be much quicker (since for Object_Id=14, there would be a total of 360 rows in A and B, and 11k rows in C).</p>
  
  <p>Thanks for any help anybody can offer!</p>