CODEWITHSUNDEEP
mysqlsqlmariadb
Milad
Milad

Reputation: 338

query optimization mariadb for nested query

I had a query that has three 'select' inside it. I guess it runs in O(n^3) and is not efficient at all, for return 10 rows is taking 6s, for 200 rows it takes 55s, and for 1000 rows it takes more than 3min, I really don't have any idea how could I make it better and faster.

mariadb version : 10.4.20-MariaDB

the query :

SELECT review.* FROM (SELECT review.* , 
                          (SELECT MIN(t2.id) FROM review t2 
                            WHERE t2.reviewcount = 0 
                            AND t2.fcid = review.fcid 
                            AND `userid` = :uid) as min_id
                      FROM review
                      WHERE reviewcount = 0 
                      AND `userid` = :uid) 
                      review
                      WHERE id = min_id AND deckid = :did LIMIT :nums;

Upvotes: 1

Views: 142

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

Your query simplified:

SELECT ...
FROM 
(
  SELECT
    review.*, 
    min(id) over (partition by userid, fcid) as min_id
  FROM review
  WHERE reviewcount = 0 
  AND userid = :uid
) r
WHERE id = min_id AND deckid = :did
LIMIT :nums;

You are looking for a user's reviews where the review count is zero. The appropriate index seems hence:

create index idx1 on review (userid, reviewcount);

This should suffice. If you want this a tad faster still, you can use a covering index instead:

create index idx2 on review (userid, reviewcount, fcid, id, deckid);

Upvotes: 1

nbk
nbk

Reputation: 49375

review should have a combined index(fcid,id,userid,deckid ,reviewcount)

The order of the columns is something you should test

SELECT r1.*       
FROM review r1
  INNER JOIN 
  (SELECT MIN(r2.id) min_id, fcid FROM review r2 
        WHERE t2.reviewcount = 0                             
        AND `userid` = :uid
        GROUP BY fcid) as r2
        ON r1.fcid = r2.fcid
  WHERE reviewcount = 0 
  AND `userid` = :uid
  AND  id = min_id AND deckid = :did 
  LIMIT :nums;

Upvotes: 1

Related Questions