-- 1. Create the main tables CREATE TABLE Book ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Title TEXT ); CREATE TABLE Author ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT ); -- 2. Create the M2M through-table CREATE TABLE Book_Authors ( id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INTEGER, author_id INTEGER, FOREIGN KEY (book_id) REFERENCES Book(ID), FOREIGN KEY (author_id) REFERENCES Author(ID) ); -- 3. Add the indexes (Fixed Typo and Names) CREATE INDEX idx_book_author_book_id ON Book_Authors(book_id); CREATE INDEX idx_book_author_author_id ON Book_Authors(author_id); CREATE UNIQUE INDEX idx_book_author_unique_book_id_author_id ON Book_Authors(book_id, author_id); -- 4. Gather statistics ANALYZE; -- 5. THE TEST EXPLAIN QUERY PLAN SELECT * FROM Book_Authors WHERE book_id = 10; CODE_BLOCK: -- 1. Create the main tables CREATE TABLE Book ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Title TEXT ); CREATE TABLE Author ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT ); -- 2. Create the M2M through-table CREATE TABLE Book_Authors ( id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INTEGER, author_id INTEGER, FOREIGN KEY (book_id) REFERENCES Book(ID), FOREIGN KEY (author_id) REFERENCES Author(ID) ); -- 3. Add the indexes (Fixed Typo and Names) CREATE INDEX idx_book_author_book_id ON Book_Authors(book_id); CREATE INDEX idx_book_author_author_id ON Book_Authors(author_id); CREATE UNIQUE INDEX idx_book_author_unique_book_id_author_id ON Book_Authors(book_id, author_id); -- 4. Gather statistics ANALYZE; -- 5. THE TEST EXPLAIN QUERY PLAN SELECT * FROM Book_Authors WHERE book_id = 10; CODE_BLOCK: -- 1. Create the main tables CREATE TABLE Book ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Title TEXT ); CREATE TABLE Author ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT ); -- 2. Create the M2M through-table CREATE TABLE Book_Authors ( id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INTEGER, author_id INTEGER, FOREIGN KEY (book_id) REFERENCES Book(ID), FOREIGN KEY (author_id) REFERENCES Author(ID) ); -- 3. Add the indexes (Fixed Typo and Names) CREATE INDEX idx_book_author_book_id ON Book_Authors(book_id); CREATE INDEX idx_book_author_author_id ON Book_Authors(author_id); CREATE UNIQUE INDEX idx_book_author_unique_book_id_author_id ON Book_Authors(book_id, author_id); -- 4. Gather statistics ANALYZE; -- 5. THE TEST EXPLAIN QUERY PLAN SELECT * FROM Book_Authors WHERE book_id = 10; CODE_BLOCK: QUERY PLAN `--SEARCH Book_Authors USING COVERING INDEX idx_book_author_unique_book_id_author_id (book_id=?) CODE_BLOCK: QUERY PLAN `--SEARCH Book_Authors USING COVERING INDEX idx_book_author_unique_book_id_author_id (book_id=?) CODE_BLOCK: QUERY PLAN `--SEARCH Book_Authors USING COVERING INDEX idx_book_author_unique_book_id_author_id (book_id=?) CODE_BLOCK: EXPLAIN QUERY PLAN SELECT * FROM Book_Authors WHERE author_id = 5; QUERY PLAN `--SEARCH Book_Authors USING INDEX idx_book_author_author_id (author_id=?) CODE_BLOCK: EXPLAIN QUERY PLAN SELECT * FROM Book_Authors WHERE author_id = 5; QUERY PLAN `--SEARCH Book_Authors USING INDEX idx_book_author_author_id (author_id=?) CODE_BLOCK: EXPLAIN QUERY PLAN SELECT * FROM Book_Authors WHERE author_id = 5; QUERY PLAN `--SEARCH Book_Authors USING INDEX idx_book_author_author_id (author_id=?) CODE_BLOCK: class Author(models.Model): name = models.CharField(max_length=100) def __str__(self): return self.name class Book(models.Model): title = models.CharField(max_length=200) authors = models.ManyToManyField(Author, related_name="books") published_date = models.DateField() def __str__(self): return f"{self.title}" CODE_BLOCK: class Author(models.Model): name = models.CharField(max_length=100) def __str__(self): return self.name class Book(models.Model): title = models.CharField(max_length=200) authors = models.ManyToManyField(Author, related_name="books") published_date = models.DateField() def __str__(self): return f"{self.title}" CODE_BLOCK: class Author(models.Model): name = models.CharField(max_length=100) def __str__(self): return self.name class Book(models.Model): title = models.CharField(max_length=200) authors = models.ManyToManyField(Author, related_name="books") published_date = models.DateField() def __str__(self): return f"{self.title}" COMMAND_BLOCK: > python manage.py makemigrations Migrations for 'blog': blog/migrations/0003_author_book.py + Create model Author + Create model Book COMMAND_BLOCK: > python manage.py makemigrations Migrations for 'blog': blog/migrations/0003_author_book.py + Create model Author + Create model Book COMMAND_BLOCK: > python manage.py makemigrations Migrations for 'blog': blog/migrations/0003_author_book.py + Create model Author + Create model Book CODE_BLOCK: ... create table etc CREATE UNIQUE INDEX "blog_book_authors_book_id_author_id_0a5bb3b3_uniq" ON "blog_book_authors" ("book_id", "author_id"); CREATE INDEX "blog_book_authors_book_id_35eae5ed" ON "blog_book_authors" ("book_id"); CREATE INDEX "blog_book_authors_author_id_fa034e3d" ON "blog_book_authors" ("author_id"); CODE_BLOCK: ... create table etc CREATE UNIQUE INDEX "blog_book_authors_book_id_author_id_0a5bb3b3_uniq" ON "blog_book_authors" ("book_id", "author_id"); CREATE INDEX "blog_book_authors_book_id_35eae5ed" ON "blog_book_authors" ("book_id"); CREATE INDEX "blog_book_authors_author_id_fa034e3d" ON "blog_book_authors" ("author_id"); CODE_BLOCK: ... create table etc CREATE UNIQUE INDEX "blog_book_authors_book_id_author_id_0a5bb3b3_uniq" ON "blog_book_authors" ("book_id", "author_id"); CREATE INDEX "blog_book_authors_book_id_35eae5ed" ON "blog_book_authors" ("book_id"); CREATE INDEX "blog_book_authors_author_id_fa034e3d" ON "blog_book_authors" ("author_id");
- If I want to search by all authors for a given book ID, this index is good enough, I will just get all nodes once I zero down the author ID.
- On the other hand, if I want to quickly search for books related to a specific author, this index won't help me at all. In this case, I would need a separate index on this table for author_id.
- One for the first entity ID
- One for the second entity ID
- One for the pair (unique constraint index)
- Django creates 3 indexes for m2m through-tables, including one for the leftmost column
- Database engines like SQLite, Postgres etc optimize usage of indexes and will prefer using the composite index when possible
