CODEWITHSUNDEEP
iphonesqlitefmdb
Peter
Peter

Reputation: 759

my SQL query runs slow need to optimise

I'm having issues with the below query for my iPhone app. When the app runs the query it takes quite a while to process the result, maybe around a second or so... I was wondering if the query can be optimised in anyway? I'm using the FMDB framework to proces all my SQL.

select pd.discounttypeid, pd.productdiscountid, pd.quantity, pd.value, p.name, p.price, pi.path 
from productdeals as pd, product as p, productimages as pi 
where pd.productid = 53252 
and pd.discounttypeid == 8769 
and pd.productdiscountid = p.parentproductid 
and pd.productdiscountid = pi.productid 
and pi.type = 362 
order by pd.id 
limit 1

My statements are below for the tables:

CREATE TABLE "ProductImages" (
    "ProductID" INTEGER,
    "Type" INTEGER,
    "Path" TEXT
)

CREATE TABLE "Product" (
    "ProductID" INTEGER PRIMARY KEY,
    "ParentProductID" INTEGER,
    "levelType" INTEGER,
    "SKU" TEXT,
    "Name" TEXT,
    "BrandID" INTEGER,
    "Option1" INTEGER,
    "Option2" INTEGER,
    "Option3" INTEGER,
    "Option4" INTEGER,
    "Option5" INTEGER,
    "Price" NUMERIC,
    "RRP" NUMERIC,
    "averageRating" INTEGER,
    "publishedDate" DateTime,
    "salesLastWeek" INTEGER
)

CREATE TABLE "ProductDeals" (
    "ID" INTEGER,
    "ProductID" INTEGER,
    "DiscountTypeID" INTEGER,
    "ProductDiscountID" INTEGER,
    "Quantity" INTEGER,
    "Value" INTEGER
)

Upvotes: 0

Views: 271

Answers (2)

socha23
socha23

Reputation: 10239

Do you have indexes on foreign key columns (productimages.productid and product.parentproductid), and the columns you use to find right product deal (productdeals.productid and productdeals.discounttypeid)? If not, that could be the cause of poor performance.

You can create them like this:

CREATE INDEX idx_images_productid ON productimages(productid);
CREATE INDEX idx_products_parentid ON products(parentproductid);
CREATE INDEX idx_deals ON productdeals(productid, discounttypeid);

Upvotes: 1

Sathish Kumar
Sathish Kumar

Reputation: 53

The below query could help you out to reduce the execution time, moreover try to create the indexes the fields correctly to fasten your query.

    select pd.discounttypeid, pd.productdiscountid, pd.quantity, pd.value, p.name,
    p.price, pi.path from productdeals pd join product p on pd.productdiscountid = 
    p.parentproductid join productimages pi on pd.productdiscountid = pi.productid where 
    pd.productid = 53252 and pd.discounttypeid = 8769 and pi.type = 362 order by pd.id
    limit 1

Thanks

Upvotes: 0

Related Questions