<commit_before>Exposition
==========

Every feature in whistle-stop detail.

------
Checks
------

Extra checks added to Django's check framework to ensure your Django and MySQL
configurations are optimal.

.. code-block:: console

    $ ./manage.py check
    ?: (django_mysql.W001) MySQL strict mode is not set for database connection 'default'
    ...

:doc:`Read more <checks>`

-------------------
QuerySet Extensions
-------------------

Django-MySQL comes with a number of extensions to ``QuerySet`` that can be
installed in a number of ways - e.g. adding the ``QuerySetMixin`` to your
existing ``QuerySet`` subclass.


Approximate Counting
--------------------

``SELECT COUNT(*) ...`` can become a slow query, since it requires a scan of
all rows; the ``approx_count`` functions solves this by returning the estimated
count that MySQL keeps in metadata. You can call it directly:

.. code-block:: python

    Author.objects.approx_count()

Or if you have pre-existing code that calls ``count()`` on a ``QuerySet`` you
pass it, such as the Django Admin, you can set the ``QuerySet`` to do try
``approx_count`` first automatically:

.. code-block:: python

    qs = Author.objects.all().count_tries_approx()
    # Now calling qs.count() will try approx_count() first

:ref:`Read more <approximate-counting>`


Query Hints
-----------

Use MySQL's query hints to optimize the SQL your ``QuerySet``\s generate:

.. code-block:: python

    Author.objects.straight_join().filter(book_set__title__startswith="The ")
    # Does SELECT STRAIGHT_JOIN ...

:ref:`Read more <query_hints>`

'Smart' Iteration
-----------------

Sometimes you need to modify every single instance of a model in a big table,
without creating any long running queries that consume large amounts of
resources. The 'smart' iterators traverse the table by slicing it into primary
key ranges which span the table, performing each slice separately, and
dynamically adjusting the slice size to keep them fast:

.. code-block:: python

    # Some authors to fix
    bad_authors = Author.objects.filter(address="Nowhere")

    # Before: bad, we can't fit all these in memory
    for author in bad_authors.all():
        pass

    # After: good, takes small dynamically adjusted slices, wraps in atomic()
    for author in bad_authors.iter_smart():
        author.address = ""
        author.save()
        author.send_apology_email()

:ref:`Read more <smart-iteration>`


Integration with pt-visual-explain
----------------------------------

For interactive debugging of queries, this captures the query that the
``QuerySet`` represents, and passes it through ``EXPLAIN`` and
``pt-visual-explain`` to get a visual representation of the query plan:

.. code-block:: pycon

    >>> Author.objects.all().pt_visual_explain()
    Table scan
    rows           1020
    +- Table
       table          myapp_author

:ref:`Read more <pt-visual-explain>`


MySQL ``HANDLER`` API
---------------------

MySQL's ``HANDLER`` commands give simple NoSQL-style read access to rows faster
than normal SQL queries, with the ability to perform index lookups or
page-by-page scans. This extension adds an ORM-based API for handlers:

.. code-block:: python

    with Author.objects.handler() as handler:
        for author in handler.iter(chunk_size=1000):
            author.send_apology_email()

:ref:`Read more <handler>`


------------
Model Fields
------------

Fields that use MySQL-specific features!

JSON Field
----------

Implements MySQL 5.7+'s JSON data type for storing arbitrary JSON data:

.. code-block:: python

    class APIResponse(Model):
        url = models.CharField(max_length=200)
        data = JSONField()

.. code-block:: pycon

    >>> APIResponse.objects.create(
    ...     url="/api/twoots/1/",
    ...     data={
    ...         "id": "123",
    ...         "message": "Loving #django and #mysql",
    ...         "coords": [34.4, 56.2],
    ...     },
    ... )
    >>> APIResponse.objects.filter(data__coords__0=34.4)
    [<APIResponse: /api/twoots/1/>]

:ref:`Read more <json-field>`

Dynamic Columns Field
---------------------

Use MariaDB's Dynamic Columns for storing arbitrary, nested dictionaries of
values:

.. code-block:: python

    class ShopItem(Model):
        name = models.CharField(max_length=200)
        attrs = DynamicField()<commit_msg>Remove JSONField from exposition (#804)<commit_after>Exposition
==========

Every feature in whistle-stop detail.

------
Checks
------

Extra checks added to Django's check framework to ensure your Django and MySQL
configurations are optimal.

.. code-block:: console

    $ ./manage.py check
    ?: (django_mysql.W001) MySQL strict mode is not set for database connection 'default'
    ...

:doc:`Read more <checks>`

-------------------
QuerySet Extensions
-------------------

Django-MySQL comes with a number of extensions to ``QuerySet`` that can be
installed in a number of ways - e.g. adding the ``QuerySetMixin`` to your
existing ``QuerySet`` subclass.


Approximate Counting
--------------------

``SELECT COUNT(*) ...`` can become a slow query, since it requires a scan of
all rows; the ``approx_count`` functions solves this by returning the estimated
count that MySQL keeps in metadata. You can call it directly:

.. code-block:: python

    Author.objects.approx_count()

Or if you have pre-existing code that calls ``count()`` on a ``QuerySet`` you
pass it, such as the Django Admin, you can set the ``QuerySet`` to do try
``approx_count`` first automatically:

.. code-block:: python

    qs = Author.objects.all().count_tries_approx()
    # Now calling qs.count() will try approx_count() first

:ref:`Read more <approximate-counting>`


Query Hints
-----------

Use MySQL's query hints to optimize the SQL your ``QuerySet``\s generate:

.. code-block:: python

    Author.objects.straight_join().filter(book_set__title__startswith="The ")
    # Does SELECT STRAIGHT_JOIN ...

:ref:`Read more <query_hints>`

'Smart' Iteration
-----------------

Sometimes you need to modify every single instance of a model in a big table,
without creating any long running queries that consume large amounts of
resources. The 'smart' iterators traverse the table by slicing it into primary
key ranges which span the table, performing each slice separately, and
dynamically adjusting the slice size to keep them fast:

.. code-block:: python

    # Some authors to fix
    bad_authors = Author.objects.filter(address="Nowhere")

    # Before: bad, we can't fit all these in memory
    for author in bad_authors.all():
        pass

    # After: good, takes small dynamically adjusted slices, wraps in atomic()
    for author in bad_authors.iter_smart():
        author.address = ""
        author.save()
        author.send_apology_email()

:ref:`Read more <smart-iteration>`


Integration with pt-visual-explain
----------------------------------

For interactive debugging of queries, this captures the query that the
``QuerySet`` represents, and passes it through ``EXPLAIN`` and
``pt-visual-explain`` to get a visual representation of the query plan:

.. code-block:: pycon

    >>> Author.objects.all().pt_visual_explain()
    Table scan
    rows           1020
    +- Table
       table          myapp_author

:ref:`Read more <pt-visual-explain>`


MySQL ``HANDLER`` API
---------------------

MySQL's ``HANDLER`` commands give simple NoSQL-style read access to rows faster
than normal SQL queries, with the ability to perform index lookups or
page-by-page scans. This extension adds an ORM-based API for handlers:

.. code-block:: python

    with Author.objects.handler() as handler:
        for author in handler.iter(chunk_size=1000):
            author.send_apology_email()

:ref:`Read more <handler>`


------------
Model Fields
------------

Fields that use MySQL-specific features!

Dynamic Columns Field
---------------------

Use MariaDB's Dynamic Columns for storing arbitrary, nested dictionaries of
values:

.. code-block:: python

    class ShopItem(Model):
        name = models.CharField(max_length=200)
        attrs = DynamicField()
{'fraction_non_alphanumeric': 0.14571499135589033, 'fraction_numerical': 0.0062978513213139044, 'mean_word_length': 2.811294117647059, 'pattern_counts': {'":': 3, '<': 17, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6231097', 'n_tokens_mistral': 2359, 'n_tokens_neox': 2178, 'n_words': 851}