Support Index creation on ForeignKeys

[vidhu]

• django-psdb-engine version: 1.0.6
• Python version: 3.9
• Operating System: OSX Monterey

Description

Turning off foreign key support in Django through

class DatabaseFeatures(MysqlBaseDatabaseFeatures):
    supports_foreign_keys = False

also disables any keys/index on columns

Many models including those from 3rd party packages make use of model.ForeignKey fields which implicitly have indexes/keys. However, given that foreign keys are disabled, this means keys are disabled as well which can lead to severe performance penalties.

Consider the following relation (adapted from this Django doc example)

class Reporter(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)
    email = models.EmailField()

class Article(models.Model):
    headline = models.CharField(max_length=100)
    reporter = models.ForeignKey(Reporter, on_delete=models.CASCADE)

Using the django-psdb-engine driver, no key is created for the Article.reporter field which means running a query like

Articles.objects.filter(reporter_id='XYZ')
# or
SELECT * FROM `app_articles` WHERE `reporter_id` = 'XYZ';

will result in a full table scan.

Fix:

Unfortunately, I don't think there's an easy straightforward way like flipping a flag to make Django auto-create indexes. Only way I see is implementing a custom schema editor

from django.db.backends.mysql.schema import DatabaseSchemaEditor
class CustomSchemaEditor(DatabaseSchemaEditor):
    # some overrides/customizations here

class DatabaseWrapper(MysqlDatabaseWrapper):
    vendor = 'planetscale'
    features_class = DatabaseFeatures
    SchemaEditorClass = CustomSchemaEditor

Users can manually specify indexes/keys but this is not ideal as this approach will not work well with external and 3rd party Django apps like django.contrib.auth