Project

General

Profile

Refactor #24256

Get rid of useless db indices and imrpove index ordering

Added by Tomer Brisker 5 months ago. Updated 4 months ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Database
Target version:
-
Difficulty:
Triaged:
No
Bugzilla link:
Team Backlog:
Fixed in Releases:
Found in Releases:

Description

Both postesql and mysql know how to use partial indices, so there is no need to have multiple indices to cover the same columns.
Additionally, in some cases the indices order can be improved, e.g. for polymorphic relations, first index the relation type then the id.


Related issues

Related to Foreman - Refactor #24573: Adding missing foreign keys and indicesReady For Testing

Associated revisions

Revision 672149ba (diff)
Added by Tomer Brisker 4 months ago

Fixes #24256 - Optimize DB indices

This removes some indices that aren't really needed since they are
already covered by parts of another index - both PostgreSQL and MySQL
know how to use a subset of a multi-column index.
Additionally, a few polymorphic indices were reversed in order so
that the type column is indexed before the id column.
A couple of duplicate indices were also deleted.

History

#1 Updated by The Foreman Bot 5 months ago

  • Status changed from New to Ready For Testing
  • Pull request https://github.com/theforeman/foreman/pull/5840 added

#2 Updated by Lukas Zapletal 4 months ago

  • Fixed in Releases 1.20.0 added

#3 Updated by Lukas Zapletal 4 months ago

  • Related to Refactor #24573: Adding missing foreign keys and indices added

#4 Updated by Tomer Brisker 4 months ago

  • Status changed from Ready For Testing to Closed

Also available in: Atom PDF