MySQL-specific Model and QuerySet extensions. To add these to your
QuerySet trifecta, see Requirements and Installation. Methods
below are all
QuerySet methods; where standalone forms are referred to,
they can be imported from
approx_count(fall_back=True, return_approx_int=True, min_size=1000)¶
By default a QuerySet’s count() method runs SELECT COUNT(*) on a table. Whilst this is fast for
InnoDBit involves a full table scan to produce a consistent number, due to MVCC keeping several copies of rows when under transaction. If you have lots of rows, you will notice this as a slow query - Percona have some more details.
This method returns the approximate count found by running
EXPLAIN SELECT COUNT(*) .... It can be out by 30-50% in the worst case, but in many applications it is closer, and is good enough, such as when presenting many pages of results but users will only practically scroll through the first few. For example:
>>> Author.objects.count() # slow 509741 >>> Author.objects.approx_count() # fast, with some error 531140
Three arguments are accepted:
Trueand the approximate count cannot be calculated,
count()will be called and returned instead, otherwise
ValueErrorwill be raised.
The approximation can only be found for
objects.all(), with no filters,
distinct()calls, etc., so it’s reasonable to fall back.
intis not returned (excpet when falling back), but instead a subclass called
ApproximateInt. This is for all intents and purposes an
int, apart from when cast to
str, it renders as e.g. ‘Approximately 12345’ (internationalization ready). Useful for templates you can’t edit (e.g. the admin) and you want to communicate that the number is not 100% accurate. For example:
>>> print(Author.objects.approx_count()) # ApproximateInt Approximately 531140 >>> print(Author.objects.approx_count() + 0) # plain int 531140 >>> print(Author.objects.approx_count(return_approx_int=False)) # plain int 531140
The threshold at which to use the approximate algorithm; if the approximate count comes back as less that this number,
count()will be called and returned instead, since it should be so small as to not bother your database. Set to
0to disable this behaviour and always return the approximation.
The default of
1000is a bit pessimistic - most tables won’t take long when calling
COUNT(*)on tens of thousands of rows, but it could be slow for very wide tables.
count_tries_approx(activate=True, fall_back=True, return_approx_int=True, min_size=1000)¶
This is the ‘magic’ method to make pre-existing code, such as Django’s admin, work with
count_tries_approxsets the QuerySet up such that then calling
approx_countinstead, with the given arguments.
To unset this, call
To ‘fix’ an Admin class with this, simply do the following (assuming
class AuthorAdmin(ModelAdmin): def get_queryset(self, request): qs = super(AuthorAdmin, self).get_queryset(request) return qs.count_tries_approx()
You’ll be able to see this is working on the pagination due to the word ‘Approximately’ appearing:
You can do this at a base class for all your
ModelAdminsubclasses to apply the magical speed increase across your admin interface.
The following methods add extra features to the ORM which allow you to access
some MySQL-specific syntax. They do this by inserting special comments which
pass through Django’s ORM layer and get re-written by a function that wraps the
Because not every user wants these features and there is a (small) overhead to every query, you must activate this feature by adding to your settings:
DJANGO_MYSQL_REWRITE_QUERIES = True
Once you’ve done this, the following methods will work.
Allows you to add an arbitrary comment to the start of the query, as the second thing after the keyword. This can be used to ‘tag’ queries so that when they show in the slow_log or another monitoring tool, you can easily back track to the python code generating the query. For example, imagine constructing a QuerySet like this:
qs = Author.objects.label("AuthorListView").all()
When executed, this will have SQL starting:
SELECT /*AuthorListView*/ ...
You can add arbitrary labels, and as many of them as you wish - they will appear in the order added. They will work in
UPDATEstatements, but not in
DELETEstatements due to limitations in the way Django performs deletes.
You should not pass user-supplied data in for the comment. As a basic protection against accidental SQL injection, passing a comment featuring
*/will raise a
ValueError, since that would prematurely end the comment. However due to executable comments, the comment is still prone to some forms of injection.
However this is a feature - by not including spaces around your string, you may use this injection to use executable comments to add hints that are otherwise not supported, or to use the new MySQL 5.7 optimizer hints.
STRAIGHT_JOINhint, which forces the join order during a
SELECT. Note that you can’t force Django’s join order, but it tends to be in the order that the tables get mentioned in the query.
# Note from Adam: sometimes the optimizer joined books -> author, which # is slow. Force it to do author -> books. Author.objects.distinct().straight_join().filter(books__age=12)[:10]
The MariaDB docs also have a good page Index Hints: How to Force Query Plans” which covers some cases when you might want to use
SQL_SMALL_RESULThint, which avoids using a temporary table in the case of a
# Note from Adam: we have very few distinct birthdays, so using a # temporary table is slower Author.objects.values('birthday').distinct().sql_small_result()
SQL_BIG_RESULThint, which forces using a temporary table in the case of a
# Note from Adam: for some reason the optimizer didn’t use a temporary # table for this, so we force it Author.objects.distinct().sql_big_result()
SQL_BUFFER_RESULThint, which forces the optimizer to use a temporary table to process the result. This is useful to free locks as soon as possible.
# Note from Adam: seeing a lot of throughput on this table. Buffering # the results makes the queries less contentious. HighThroughputModel.objects.filter(x=y).sql_buffer_result()
SQL_CACHEhint, which means the result set will be stored in the Query Cache. This only has an effect when the MySQL system variable
query_cache_typeis set to
# Fetch recent posts, cached in MySQL for speed recent_posts = BlogPost.objects.sql_cache().order_by('-created')[:5]
SQL_NO_CACHEhint, which means the result set will not be fetched from or stored in the Query Cache. This only has an effect when the MySQL system variable
query_cache_typeis set to
# Avoid caching all the expired sessions, since we’re about to delete # them deletable_session_ids = ( Session.objects.sql_no_cache() .filter(expiry__lt=now()) .values_list('id', flat=True) )
SQL_CALC_FOUND_ROWShint, which means the total count of matching rows will be calculated when you only take a slice. You can access this count with the
found_rowsattribute of the
QuerySetafter filling its result cache, by e.g. iterating it.
This can be faster than taking the slice and then again calling
.count()to get the total count.
>>> can_drive = Customer.objects.filter(age=21).sql_calc_found_rows()[:10] >>> len(can_drive) # Fetches the first 10 from the database 10 >>> can_drive.found_rows # The total number of 21 year old customers 1942
use_index(*index_names, for_=None, table_name=None)¶
USE INDEXhint, which affects the index choice made by MySQL’s query optimizer for resolving the query.
Note that index names on your tables will normally have been generated by Django and contain a hash fragment. You will have to check your database schema to determine the index name.
If you pass any non-existent index names, MySQL will raise an error. This means index hints are especially important to test in the face of future schema changes.
for_restricts the scope that the index hint applies to. By default it applies to all potential index uses during the query; you may supply one of
'ORDER BY', or
'GROUP BY'to restrict the index hint to only be used by MySQL for index selection in their respective stages of query execution. For more information see the MySQL/MariaDB docs (link below).
table_nameis the name of the table that the hints are for. By default, this will be the name of the table of the model that the
QuerySetis for, however you can supply any other table that may be joined into the query (from e.g.
select_related()). Be careful - there is no validation on the table name, and if it does not exist in the final query it will be ignored. Also it is injected raw into the resultant SQL, so you should not use user data otherwise it may open the potential for SQL injection.
USE INDEXaccepts no index names to mean ‘use no indexes’, i.e. table scans only.
# SELECT ... FROM `author` USE INDEX (`name_12345`) WHERE ... >>> Author.objects.use_index('name_12345').filter(name='John') # SELECT ... FROM `author` USE INDEX (`name_12345`, `name_age_678`) WHERE ... >>> Author.objects.use_index('name_12345', 'name_age_678').filter(name='John') # SELECT ... FROM `author` USE INDEX FOR ORDER BY (`name_12345`) ... ORDER BY `name` >>> Author.objects.use_index('name_12345', for_='ORDER BY').order_by('name') # SELECT ... FROM `book` INNER JOIN `author` USE INDEX (`authbook`) ... >>> Book.objects.select_related('author').use_index('authbook', table_name='author')
Similar to the above
use_index(), but adds a
FORCE INDEXhint. Note that unlike
use_index()you must supply at least one index name. For more information, see the MySQL/MariaDB docs.
Similar to the above
use_index(), but adds an
IGNORE INDEXhint. Note that unlike
use_index()you must supply at least one index name. For more information, see the MySQL/MariaDB docs.
Here’s a situation we’ve all been in - we screwed up, and now we need to fix the data. Let’s say we accidentally set the address of all authors without an address to “Nowhere”, rather than the blank string. How can we fix them??
The simplest way would be to run the following:
Unfortunately with a lot of rows (‘a lot’ being dependent on your database server and level of traffic) this will stall other access to the table, since it will require MySQL to read all the rows and to hold write locks on them in a single query.
To solve this, we could try updating a chunk of authors at a time; such code tends to get ugly/complicated pretty quickly:
min_id = 0 max_id = 1000 biggest_author_id = Author.objects.order_by('-id').id while True: Author.objects.filter(id__gte=min_id, id__lte=BLA BLA BLA # I'm not even going to type this all out, it's so much code
Here’s the solution to this boilerplate with added safety features - ‘smart’
iteration! There are two classes; one yields chunks of the given
and the other yields the objects inside those chunks. Nearly every data update
can be thought of in one of these two methods.
SmartChunkedIterator(queryset, atomically=True, status_thresholds=None, pk_range=None, chunk_time=0.5, chunk_size=2, chunk_min=1, chunk_max=10000, report_progress=False, total=None)¶
Implements a smart iteration strategy over the given
queryset. There is a method
iter_smart_chunksthat takes the same arguments on the
QuerySetMixinso you can just:
bad_authors = Author.objects.filter(address="Nowhere") for author_chunk in bad_authors.iter_smart_chunks(): author_chunk.update(address="")
Iteration proceeds by yielding primary-key based slices of the queryset, and dynamically adjusting the size of the chunk to try and take
chunk_timeseconds. In between chunks, the
GlobalStatusis called to ensure the database is not under high load.
Because of the slicing by primary key, there are restrictions on what
QuerySets you can use, and a
ValueErrorwill be raised if the queryset doesn’t meet that. Specifically, only
QuerySets on models with integer-based primary keys, which are unsliced, and have no
There are a lot of arguments and the defaults have been picked hopefully sensibly, but please check for your case though!
The queryset to iterate over; if you’re calling via
.iter_smart_chunksthen you don’t need to set this since it’s the queryset you called it on.
If true, wraps each chunk in a transaction via django’s
transaction.atomic(). Recommended for any write processing.
A dict of status variables and their maximum tolerated values to be checked against after each chunk with
When set to
None, it lets
GlobalStatususe its default of
'Threads_running': 5}. Set to an empty dict to disable status checking (not really recommended, it doesn’t add much overhead and can will probably save your butt one day).
Controls the primary key range to iterate over with slices. By default, with
pk_range=None, the QuerySet will be searched for its minimum and maximum
pkvalues before starting. On QuerySets that match few rows, or whose rows aren’t evenly distributed, this can still execute a long blocking table scan to find these two rows. You can remedy this by giving a value for
- If set to
'all', the range will be the minimum and maximum PK values of the entire table, excluding any filters you have set up - that is, for
Model.objects.all()for the given
- If set to a 2-tuple, it will be unpacked and used as the minimum and maximum values respectively.
The iterator determines the minimum and maximum at the start of iteration and does not update them whilst iterating, which is normally a safe assumption, since if you’re “fixing things” you probably aren’t creating any more bad data. If you do need to process every row then set
pk_rangeto have a maximum far greater than what you expect would be reached by inserts that occur during iteration.
- If set to
The time in seconds to aim for each chunk to take. The chunk size is dynamically adjusted to try and match this time, via a weighted average of the past and current speed of processing. The default and algorithm is taken from the analogous
The initial size of the chunk that will be used. As this will be dynamically scaled and can grow fairly quickly, the initial size of 2 should be appropriate for most use cases.
The minimum number of objects in a chunk. You do not normally need to tweak this since the dynamic scaling works very well, however it might be useful if your data has a lot of “holes” or if there are other constraints on your application.
The maximum number of objects in a chunk, a kind of sanity bound. Acts to prevent harm in the case of iterating over a model with a large ‘hole’ in its primary key values, e.g. if only ids 1-10k and 100k-110k exist, then the chunk ‘slices’ could grow very large in between 10k and 100k since you’d be “processing” the non-existent objects 10k-100k very quickly.
If set to true, display out a running counter and summary on
sys.stdout. Useful for interactive use. The message looks like this:
AuthorSmartChunkedIterator processed 0/100000 objects (0.00%) in 0 chunks
\rto erase itself when re-printing to avoid spamming your screen. At the end
Finished!is printed on a new line.
By default the total number of objects to process will be calculated with
count()query could potentially be big and slow.
totalallows you to pass in the total number of objects for processing, if you can calculate in a cheaper way, for example if you have a read-replica to use.
A convenience subclass of
SmartChunkedIteratorthat simply unpacks the chunks for you. Can be accessed via the
For example, rather than doing this:
bad_authors = Author.objects.filter(address="Nowhere") for authors_chunk in bad_authors.iter_smart_chunks(): for author in authors_chunk: author.send_apology_email()
You can do this:
bad_authors = Author.objects.filter(address="Nowhere") for author in bad_authors.iter_smart(): author.send_apology_email()
All the same arguments as
A subclass of
SmartChunkedIteratorthat doesn’t return the chunk’s
QuerySetbut instead returns the start and end primary keys for the chunk. This may be useful when you want to iterate but the slices need to be used in a raw SQL query. Can be accessed via the
For example, rather than doing this:
for authors_chunk in Author.objects.iter_smart_chunks(): limits = author_chunk.aggregate(min_pk=Min('pk'), max_pk=Max('pk')) authors = Author.objects.raw(""" SELECT name from app_author WHERE id >= %s AND id <= %s """, (limits['min_pk'], limits['max_pk'])) # etc...
...you can do this:
for start_pk, end_pk in Author.objects.iter_smart_pk_ranges(): authors = Author.objects.raw(""" SELECT name from app_author WHERE id >= %s AND id < %s """, (start_pk, end_pk)) # etc...
In the first format we were forced to perform a dumb query to determine the primary key limits set by
SmartChunkedIterator, due to the
QuerySetnot otherwise exposing this information.
There is a subtle difference between the two versions. In the first the end boundary,
max_pk, is a closed bound, whereas in the second, the
iter_smart_pk_rangesis an open bound. Thus the
<=changes to a
All the same arguments as
Integration with pt-visual-explain¶
How does MySQL really execute a query? The
(docs: MySQL /
gives a description of the execution plan, and the
can format this in an understandable tree.
This function is a shortcut to turn a
QuerySet into its visual explanation,
making it easy to gain a better understanding of what your queries really end
Call on a
QuerySetto print its visual explanation, or with
display=Falseto return it as a string. It prepends the SQL of the query with ‘EXPLAIN’ and passes it through the
pt-visual-explaincommands to get the output. You therefore need the MySQL client and Percona Toolkit installed where you run this.
>>> Author.objects.all().pt_visual_explain() Table scan rows 1020 +- Table table myapp_author
Can also be imported as a standalone function if you want to use it on a
QuerySetthat does not have the
QuerySetMixinadded, e.g. for built-in Django models:
>>> from django_mysql.models import pt_visual_explain >>> pt_visual_explain(User.objects.all()) Table scan rows 1 +- Table table auth_user
This extension adds an ORM-based API for handlers. You can instantiate them
QuerySet (and thus from .objects), and open/close them as context
with Author.objects.handler() as handler: first_author_by_pk = handler.read() first_ten_authors_by_pk = handler.read(limit=10) for author in handler.iter(chunk_size=1000): author.send_apology_email()
.handler() method simply returns a
Handler instance; the class can
be imported and applied to
QuerySets from models without the extensions
easily as well:
from django_mysql.models.handler import Handler with Handler(User.objects.all()) as handler: for user in handler.iter(chunk_size=1000): user.send_notification_email()
HANDLER is lower level than
SELECT, and has some optimizations
that mean it permits ‘for example’ dirty reads. Check the database
documentation and understand the consequences of this before you replace
any SQL queries!
Implements a handler for the given queryset’s model. The
WHEREclause and query parameters, if they exist, will be extracted from the queryset’s SQL. Since
HANDLERstatements can only operate on one table at a time, only relatively simple querysets can be used - others will result in a
Handleris only opened and available for reads when used as a context manager. You may have multiple handlers open at once, even on the same table, but you cannot open the same one twice.
read(index='PRIMARY', value__LOOKUP=None, mode=None, where=None, limit=None)¶
Returns the result of a
HANDLER .. READstatement as a
RawQuerySetfor the given
queryset‘s model (which, like all
QuerySets, is lazy).
HANDLERstatements must select whole rows, therefore there is no way of optimizing by returning only certain columns (like
MySQL has three forms of
HANDLER .. READstatements, but only the first two forms of
HANDLER .. READstatements are supported - you can specify index lookups, or pagination. The third form, ‘natural row order’, only makes sense for MyISAM tables.
The name of the index of the table to read, defaulting to the primary key. You must provide the index name as known by MySQL, not the names of the indexed column[s] as Django’s
index_togetherlet you specify. This will only be checked by MySQL so an
OperationalErrorwill be raised if you specify a wrong name.
Both single and multi-column indexes are supported.
The ‘first form’ of
HANDLER .. READsupports index lookups.
value__LOOKUPallows you to specify a lookup on
indexusing the same style as Django’s ORM, and is mutually exclusive with
mode. You may only have one index lookup on a
read- other conditions must be filtered with
where. For example:
# Read objects with primary key <= 100 handler.read(value__lte=100, limit=100)
The valid lookups are:
value__lt=x- index value
value__lte=x- index value
value__exact=x- index value
value__gte=x- index value
value__gt=x- index value
For single-column indexes, specify the value; for multi-column indexes, specify an iterable of values, one for each column, in index order. For example:
grisham = handler.read(index='full_name_idx', value=('John', 'Grisham'))
The ‘second form’ of
HANDLER .. READsupports paging over a table, fetching one batch of results at a time whilst the handler object on MySQL’s end retains state, somewhat like a ‘cursor’. This is mutually exclusive with
value__LOOKUP, and if neither is specified, this is the default.
There are four modes:
first- commence iteration at the start
next- continue ascending/go forward one page
last- commence iteration at the end (in reverse)
prev- continue descending/go backward one page
To iterate forwards, use
'first'and then repeatedly
'next'. To iterate backwards, use
'last'and then repeatedly
'prev'. The page size is set with
N.B. the below
itermethod below is recommended for most iteration.
HANDLER .. READstatements support
WHEREclauses for columns on the same table, which apply after the index filtering. By default the
WHEREclause from the
querysetused to construct the
Handlerwill be applied. Passing a different
whereallows you to read with different filters. For example:
with Author.objects.handler() as handler: old = Author.objects.filter(age__gte=50) first_old_author = handler.read(where=old) young = Author.objects.filter(age__lte=50) first_young_author = handler.read(where=young)
By default every
HANDLER .. READstatement returns only the first row. Specify
limitto retrieve a different number of rows.
iter(index='PRIMARY', where=None, chunk_size=100, reverse=False)¶
Iterate over a table via the named index, one chunk at a time, yielding the individual objects. Acts as a wrapper around repeated calls to
The name of the index to iterate over. As detailed above, this must be the index name on MySQL.
QuerySetfor filter conditions, the same as
whereargument, as detailed above.
The size of the chunks to read during iteration.
The direction of iteration over the index. By default set to
True, the index will be iterated in ascending order; set to
False, the index will be iterated in descending order.
You can only have one iteration happening at a time per
Handler, otherwise on the MySQL side it loses its position. There is no checking for this in