0

I made a query upon 1 million data, which worked just fine. But when I use the full data , the query ran hours or even longer.. I am trying to figure out what were the reasons for the slow query, but I really don‘t know where to start, except that I read to check the indices... Could anyone give me a direction? Thanks a looooot!

Query statement:

select count(*) from table1, table3 on id=table3.table1_id where table3.table2_id = 123 and id in (134,267,390,4234) and item = 30;

table structure:

table1:
   id integer primary key,
   item integer
   
table2:
   id integer,
   item integer

table3:
    table1_id integer,
    table2_id integer

-- the DB without index was 0.8 TB after the three indices is now 2.5 TB
indices on: table1.item, (table1.item, table1.id), table3.table1_id, table3.table2_id, (table3.table1_id, table3.table2_id)

env: Linux, sqlite 3.7.17

Erwin Zangwill
  • 45
  • 7
  • 2
    Why are you choosing not to use proper, explicit, **standard**, readable `JOIN` syntax? – Gordon Linoff Jul 23 '20 at 10:48
  • See https://stackoverflow.com/questions/1454188/how-can-i-analyse-a-sqlite-query-execution and https://www.sqlite.org/lang_analyze.html – marco.m Jul 23 '20 at 15:59

0 Answers0