SQL Indexes

sqlite-indexes.sql

/*
    SQL Indexes (specifically SQLite)

    SQLite version: 3.45.1

    Bill Wallis, 2024-07-27

    https://sqlite.org/queryplanner.html
    https://use-the-index-luke.com/
*/


select sqlite_version();


drop table if exists fruits_for_sale;
create table fruits_for_sale(
  fruit text,
  state text,
  price real
);
insert into fruits_for_sale(rowid, fruit, state, price)
values
    ( 1, 'Orange',     'FL', 0.85),
    ( 2, 'Apple',      'NC', 0.45),
    ( 4, 'Peach',      'SC', 0.60),
    ( 5, 'Grape',      'CA', 0.80),
    (18, 'Lemon',      'FL', 1.25),
    (19, 'Strawberry', 'NC', 2.45),
    (23, 'Orange',     'CA', 1.05)
;

create index fruits_for_sale__fruit               on fruits_for_sale (fruit);
create index fruits_for_sale__state               on fruits_for_sale (state);
create index fruits_for_sale__fruit__state        on fruits_for_sale (fruit, state);
create index fruits_for_sale__fruit__state__price on fruits_for_sale (fruit, state, price);
create index fruits_for_sale__fruit__price__fl    on fruits_for_sale (fruit, price) where state = 'FL';
analyze;

pragma index_list(fruits_for_sale);


/* fruits_for_sale */
select rowid, *
from fruits_for_sale
order by rowid
;

/* fruits_for_sale__fruit */
select fruit, rowid
from fruits_for_sale
order by fruit, rowid
;

/* fruits_for_sale__state */
select state, rowid
from fruits_for_sale
order by state, rowid
;

/* fruits_for_sale__fruit__state */
select fruit, state, rowid
from fruits_for_sale
order by fruit, state, rowid
;

/* fruits_for_sale__fruit__state__price */
select fruit, state, price, rowid
from fruits_for_sale
order by fruit, state, price, rowid
;

/* fruits_for_sale__fruit__price__fl */
select fruit, price, rowid
from fruits_for_sale
where state = 'FL'
order by fruit, price, rowid
;


/* covered by the `fruits_for_sale__fruit__price__fl` index */
explain query plan
    select fruit, price
    from fruits_for_sale
    where state = 'FL'
    order by fruit, price
;

/* covered by the `fruits_for_sale__fruit` index */
explain query plan
    select distinct fruit
    from fruits_for_sale
;

This is part of the following LinkedIn post:

https://www.linkedin.com/posts/bill-wallis_sql-analyticsengineering-dataengineering-activity-7223009253043699712-eI_D