Indices are a general concept in computing. This book also has an index for faster lookup, which matches concepts to page numbers. An index takes up space; in the case of this book, a couple of pages. Database indices have the added disadvantage that they make inserts and updates slower because of the extra overhead of updating the index. Usually, primary and foreign keys automatically get an index, but this depends on the database implementation.
Adding indices should not be taken lightly, and this is best done after consulting database administrators. Alembic has features for index addition similar to the features that we saw in the Adding a table column to an existing table recipe.
This recipe has some overlap with the Adding a table column to an existing table recipe, so I will not repeat all the details:
$ alembic revision -m "Add indices"
21579ecccd8_add_indices.py
) and modify the code to have the following functions, which take care of adding indices:def upgrade(): # MODIFIED Ivan Idris op.create_index('idx_links_url', 'links', ['url']) op.create_index('idx_pages_filename', 'pages', ['filename']) def downgrade(): # MODIFIED Ivan Idris op.drop_index('idx_links_url') op.drop_index('idx_pages_filename')
$ alembic upgrade head --sql
The create_index()
function adds indices given an index name, a table, and a list of table columns. The drop_index()
function does the opposite, removing indices given an index name.