Index
- 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','john.doe@sqlitetutorial.net');
>INSERT INTO contacts (first_name, last_name, email) VALUES('Johny','Doe','john.doe@sqlitetutorial.net');
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','david.brown@sqlitetutorial.net'),
('Lisa','Smith','lisa.smith@sqlitetutorial.net');
>EXPLAIN QUERY PLAN
SELECT * FROM contacts WHERE email = 'lisa.smith@sqlitetutorial.net';
>.quit
