CODEWITHSUNDEEP
sqlite
Josef Ondrej
Josef Ondrej

Reputation: 159

over - partition by in SQLite

I have a table TABLE in SQLite database with columns DATE, GROUP. I want to select the first 10 entries in each group. After researching similar topics here on stackoverflow, I came up with the following query, but it runs very slowly. Any ideas how to make it faster?

select * from TABLE as A 
where (select count(*) from TABLE as B 
       where B.DATE < A.DATE and A.GROUP == B.GROUP) < 10

This is the result of EXPLAIN QUERY PLAN (TABLE = clients_bets):

The result of EXPLAIN QUERY PLAN

Upvotes: 1

Views: 1998

Answers (1)

Philippe Banwarth
Philippe Banwarth

Reputation: 17755

Here are a few suggestions :

  • Use a covering index (an index containing all the data needed in the subquery, in this case the group and date)

    create index some_index on some_table(some_group, some_date)

  • Additionally, rewrite the subquery to make is less dependent on outer query :

    select * from some_table as A
where rowid in (
    select B.rowid
    from some_table as B 
    where A.some_group == B.some_group 
    order by B.some_date limit 10 )

    The query plan change from :

    0   0   0   SCAN TABLE some_table AS A
0   0   0   EXECUTE CORRELATED LIST SUBQUERY 1
1   0   0   SEARCH TABLE some_table AS B USING COVERING INDEX idx_1 (some_group=?)

    to

    0   0   0   SCAN TABLE some_table AS A  
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
1   0   0   SEARCH TABLE some_table AS B USING COVERING INDEX idx_1 (some_group=? AND some_date<?)

    While it is very similar, the query seems quite faster. I'm not sure why.

Upvotes: 3

Related Questions