Understanding Django's Initial Database Queries

The queries you're observing are part of Django's database connection initialization process when working with MySQL. Let me explain what these queries are doing and how you might optimize this behavior.

What These Queries Are

1. Transaction Isolation Level Setting:

   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
   

   This query sets the transaction isolation level to "read committed," which is Django's preferred isolation level for MySQL. As mentioned in the documentation, Django works best with "read committed" rather than MySQL's default "repeatable read" to prevent potential data loss issues.

2. Database Feature Detection:

   SELECT VERSION(), sql_mode, default_storage_engine, sql_auto_is_null, lower_case_table_names, CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
   

   This query helps Django determine the capabilities and configuration of your MySQL server to adjust its behavior accordingly.

Optimization Options

These queries are executed when Django establishes a new database connection. Here are some approaches to minimize their impact:

1. Use Database Connection Pooling

The most effective solution is to implement connection pooling, which reuses existing database connections rather than creating new ones for each request. This way, these initialization queries only run when a new connection is created, not on every request.

You can use packages like django-db-connection-pool or configure a connection pooler like PgBouncer (though for MySQL, you might look at ProxySQL or MySQL Router).

2. Persistent Connections

You can enable persistent connections in your Django settings:

PYTHONCopied
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        # other settings...
        'CONN_MAX_AGE': 600,  # Keep connections open for 10 minutes
    }
}

This keeps connections open for the specified number of seconds, reducing the need to establish new connections.

3. Pre-configure MySQL Settings

You can set the isolation level in your MySQL configuration or in your Django database settings:

PYTHONCopied
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        # other settings...
        'OPTIONS': {
            'isolation_level': 'read committed',
            # This pre-configures the isolation level
        }
    }
}

Important Considerations

• The time: '0.000' output suggests these queries are very fast, but if you're making many short-lived connections, the overhead can add up.
• Completely disabling these queries is not recommended as they ensure proper database behavior with Django.
• If you're in a high-performance environment, connection pooling is generally the best approach.

Remember that Django's default settings are chosen for data integrity and compatibility. Changing isolation levels or other database settings should be done with caution to avoid unexpected behavior.