SQLite queries taking a long time - Angular Ionic-CodePudding

I have a ionic angular application and I am using SQLite as the local database.

I have the following query:

const statement = '  SELECT fl.*, ai.*, fl.uuid as fluuid '  
  '(SELECT aName FROM world WHERE world.uuid = fl.daUUID) as depName, '  
  '(SELECT country FROM world WHERE world.uuid = fl.daUUID) as depCountry, '  
  '  FROM fl, ai WHERE fl.userUUID= ? AND ai.uuid=fl.aiUUID  ORDER BY Date ASC';

I have the following create statements for then the app starts:

table fl


           `create TABLE IF NOT EXISTS  fl
    (
        ID                   int                                 primary key,
        daUUID               text                                null,
        aaUUID               text                                null,
        aiUUID               text                                null,
        uuid                 text                                null,
        userUUID             text                                null,
        updateTimeStamp      DATETIME not null DEFAULT CURRENT_TIMESTAMP
    );
    create index IF NOT EXISTS ix1Fl1
        on fl (userUUID, aiUUID, uuid);
    create index IF NOT EXISTS ix1Fl2
        on fl (daUUID, aaUUID);
        `

table ai


           `create TABLE IF NOT EXISTS  ai
    (
        aiID                  int                                 primary key,
        uuid                  TEXT                                null,
        userUUID              TEXT                                null,
        updateTimeStamp       DATETIME not null DEFAULT CURRENT_TIMESTAMP
    
    );
    create index IF NOT EXISTS ix1Ai1 on ai (uuid, userUUID);
          );`

table world


    `
    create table IF NOT EXISTS world
    (
        aiID            int                                 primary key,
        uuid            text                                null,
        userUUID        text                                null,
        updateTimeStamp timestamp default CURRENT_TIMESTAMP
    );create index IF NOT EXISTS ix1world1
        on world (uuid, userUUID);`

as seen I have added the index but (just like my live db) but the query runs slow,

but if I remove this from the query

  '(SELECT aName FROM world WHERE world.uuid = fl.daUUID) as depName, '  
  '(SELECT country FROM world WHERE world.uuid = fl.daUUID) as depCountry, '

the query runs fast again.

Wondering what the solution is for this? (The same concept works for my live db very fast)

(NOTE: I have cut the create statement down as they would be too long to post)

Thank you

CodePudding user response：

You could replace the two correlated subqueries with one join :

SELECT fl.*, ai.*, fl.uuid as fluuid,
world.aName AS depName,
world.country as depCountry
FROM fl, ai
JOIN world ON world.uuid = fl.daUUID
WHERE fl.userUUID= ? AND ai.uuid = fl.aiUUID
ORDER BY Date ASC

or even :

SELECT fl.*, ai.*, fl.uuid as fluuid,
world.aName AS depName,
world.country as depCountry
FROM fl
JOIN ai ON ai.uuid = fl.aiUUID
JOIN world on world.uuid = fl.daUUID
WHERE fl.userUUID = ?
ORDER BY Date ASC