Databases
StackOverflow2013
Postsdbo
POSQL: How to improve performance when filtering rows based on foreign data?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL: How to improve performance when filtering rows based on foreign data?
    primarykey
    data
    text
    <p>Say I have a schema that represents a fixed-depth hierarchy like this:</p> <pre><code> CREATE TABLE level0 ( id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL ) CREATE TABLE level1 ( id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL, level0_id INTEGER NOT NULL ) CREATE TABLE level2 ( id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL, level1_id INTEGER NOT NULL, is_important INTEGER ) CREATE INDEX level2_level1_id ON level2 (level1_id) CREATE INDEX level1_level0_id ON level1 (level0_id) </code></pre> <p>(Just to give a sense of scale, assume 1000 rowsin level0, 2000 in level1, and 20000 in level2 and this is a sqlite database on an phone's sd card. Level 0 queries return up to 1000 rows, level1 queries return 1-30 rows, and level2 queries return 1-20 rows)</p> <p>I'm displaying this hierarchy one level at a time. So my queries for displaying each of the three levels look like this:</p> <pre><code> SELECT id,text FROM level0 SELECT id,text FROM level1 WHERE level0_id = 12345 SELECT id,text FROM level2 WHERE level1_id = 23456 </code></pre> <p>Simple, fast, and fully indexed. Now, I also want to display the same hierarchy, except I want to filter it based on is_important. I only want to display level0 and level1 rows that eventually lead to level2 rows with is_important = 1.</p> <p>So I write some new queries, very different from the old ones.</p> <pre><code> level 0: SELECT DISTINCT l0.id,l0.text FROM level2 AS l2 INNER JOIN level1 AS l1 ON l1.id = l2.level1_id INNER JOIN level0 as l0 on l0.id = l1.level0_id WHERE l2.is_important = 1 level 1: SELECT DISTINCT l1.id,l1.text FROM level2 AS l2 INNER JOIN level1 AS l1 ON l1.id = l2.level1_id WHERE l2.is_important = 1 level 2: SELECT id,text FROM level2 WHERE level1_id = 23456 AND is_important = 1 </code></pre> <p>The level 0 and level 1 queries are obviously much, much, slower than the unfiltered queries above. I understand why they are slow, but I'm having trouble improving their performance.</p> <p>I feel strange starting the query by walking the largest table to extract the smaller ones, but this seems like the most succinct and natural way to express what I want in terms that SQL can understand. </p> <p>So my question is this: How would you improve the performance of the filtered level 0 and level 1 queries above?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USblucz
    1. USblucz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL: How to improve performance when filtering rows based on foreign data?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload