[SOLVED] Why does my sqlite `update set` take too much time?

Why does my sqlite `update set` take too much time?

i have two tables called product and image as you can see below:

CREATE TABLE "product" (
  "Id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "Image" TEXT
);
CREATE TABLE "image" (
  "Id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "IsCover" INTEGER,
  "Url" TEXT,
  "Product" INTEGER,
  FOREIGN KEY ("Product") REFERENCES "product" ("Id")
);

product table contains 350,000 or so rows, and image table contains over 500,000 rows.

I want to transfer images corresponding to my products to product.Image, so i used this line of code but it takes forever to run.

update product
set image = (select Url from image
             where IsCover = 1 and
                   Product = product.Id
             limit 1);

Is there a faster way to achieve this? Why is this way so slow?

Solution

I would first begin by commenting that using LIMIT 1 without an ORDER BY clause in general does not make much sense. It is clear that you expect in some cases the subquery would return more than one row. But, the best thing to do would be fix the subquery using other logic to ensure that only one match would ever be found.

That being said, to speed up the correlated subquery, you might try one of the following two index suggestions:

CREATE INDEX idx1 ON image (Product, IsCover, Url);
or
CREATE INDEX idx2 ON image (IsCover, Product, Url);

Since the lookup in the subquery should be by far the biggest performance bottleneck, adding the right index could make a world of difference.