CB110 course, Lab 8 question

SQL++
1

Object C
11. (Challenge) Create an index or indexes to optimize performance of this query.

I tried to create index

create index idx_rating on couchmusic2(
array rating.username for rating in ratings end, array rating.rating for rating in ratings end)
where type = ‘track’

But the query doesnt use index and is very slow

SELECT artist, genre, title, id
FROM couchmusic2
use index(idx_rating)
WHERE type = ‘track’
AND ANY rating in ratings SATISFIES
rating.username = ‘conwormish43746’ AND rating.rating = 5 END

2 
CREATE INDEX idx_rating1 ON couchmusic2(DISTINCT ARRAY rating.username FOR rating in ratings END,ratings, artist, genre, title, id)
WHERE type = "track";

CREATE INDEX idx_rating2 ON couchmusic2(DISTINCT ARRAY rating.rating FOR rating in ratings END,ratings, artist, genre, title, id)
WHERE type = “track”;

  1. You need ALL or DISTINCT keyword is needed before ARRAY keyword
  2. You can only create single array index key in index
  3. If you want you can create two different indexes and it uses INTERSECT scan
  4. if you are going to use equality predicates
    CREATE INDEX idx_rating3 ON couchmusic2(DISTINCT ARRAY [rating.username, rating.rating] FOR rating in ratings END, artist, genre, title, id)
    WHERE type = “track”;
    SELECT artist, genre, title, id
    FROM couchmusic2
    WHERE type = ‘track’
    AND ANY rating in ratings SATISFIES [rating.username,rating.rating] = [“conwormish43746”, 5] END;
3

Thanks, it indeed works