sqlite - 如何将连接与 Sqlite 索引一起使用

Question

我正在使用 ionic 3 创建一个应用程序。在应用程序中，我添加了一个搜索过滤器，它根据用户的角色过滤用户。我有 5 个表，我必须从中提取数据。为此，我做了这些步骤..

使用所需数据创建每个表索引。

CREATE INDEX IF NOT EXISTS AccountIndexTable ON Accounts(id,roleId,firstName,lastName,email,accountId);
CREATE INDEX IF NOT EXISTS AddressIndexTable ON Addresses(id,addressTypeId,street,city,state,country,pincode,accountId)
CREATE INDEX IF NOT EXISTS CompanyIndexTable ON Companies(id,name,accountId)
CREATE INDEX IF NOT EXISTS CommunicationIndexTable ON Communications(id,phone,accountId)
CREATE INDEX IF NOT EXISTS OptionalInfoIndexTable ON CustomerOptionalInfos(id,customerType,accountId)

然后我用加入来获取数据。在此之前我有问题

1：我需要在我的连接中使用索引表吗？

加入查询：

SELECT Accounts.accountId AS accountID,
   Accounts.roleId AS roleID,
   Accounts.email AS email,
   Accounts.firstName || " " || Accounts.lastName AS name,
   Addresses.street AS street,
   Addresses.city AS city,
   Addresses.state AS state,
   Addresses.country AS country,
   Addresses.pincode AS pincode,
   Communications.phone AS phone,
   Companies.name AS compName,
   CustomerOptionalInfos.customerType AS cType
FROM Accounts
LEFT JOIN Addresses ON Addresses.accountId=Accounts.accountId
AND Addresses.addressTypeId=1
LEFT JOIN Communications ON Communications.accountId=Accounts.accountId
LEFT JOIN Companies ON Companies.accountId=Accounts.accountId
LEFT JOIN CustomerOptionalInfos ON CustomerOptionalInfos.accountId=Accounts.accountId
WHERE 1=1
  AND Accounts.isDelete!='true'
  AND Accounts.firstName!= ''
  AND Accounts.roleId = 5
ORDER BY Accounts.firstName ASC
LIMIT ?,?

执行仍然需要 +2 秒。请建议我这样做的更好方法。我有大量的数据。

标签： sqlitejoinindexing