- Indexes to query data faster, speed up sort operation, and enforce unique constraints.
- A DB table each row has rowid and sequence number to identify row
- Eg :table = list of pairs (rowid, row) )
- Index is created on a seperate table which has opposite relationship: (row, rowid)
- SQLite uses B-tree ie., balanced-tree ie., Actual table rows = Index table rows
Mac:
$brew install sqlite3
>sqlite3
>CREATE TABLE contacts (
first_name text NOT NULL,
last_name text NOT NULL,
email text NOT NULL
);
>CREATE UNIQUE INDEX idx_contacts_email ON contacts (email);
>INSERT INTO contacts (first_name, last_name, email) VALUES('John','Doe','[email protected]');
>INSERT INTO contacts (first_name, last_name, email) VALUES('Johny','Doe','[email protected]');
SQLite issued an error message indicating that the unique index has been violated. Note :email shd be unique across
>INSERT INTO contacts (first_name, last_name, email)
VALUES('David','Brown','[email protected]'),
('Lisa','Smith','[email protected]');
>EXPLAIN QUERY PLAN
SELECT * FROM contacts WHERE email = '[email protected]';
>.quit