# Reducing queries for ForeignKeys in Django admin inlines

### Speed up with raw\_id\_fields

ForeignKeys in the Django admin show as a select-box (<select>) by default. This will result in a query for every item in the select, only to get the name. This is not a problem if the model you're referencing will always be small. But growing models with significant rows will become a problem soon. Having 3 times the same select-box with 1000 items will result in 3000 queries. It will also make the select unusable.

Instead, you could use the `ModelAdmin.raw_id_fields` which turns it into an input widget with a search icon. It will only query the name of the selected item:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1673363860522/1896e32a-2d81-4fe6-b602-6d82dcf6fa67.png align="center")

### Prefetching in inlines

`raw_id_fields` can also be used in ModelAdmin inlines. However, when you have a lot of these inlines on a page, it's gonna hurt. Having 180 items will result in at least that amount of queries, and usually even double or more.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1673364347375/8e29e55d-4bea-417b-925a-f26f45d42cfb.png align="center")

Less queries = faster load, so to cope with this you can use `prefetch_related` which I [wrote about earlier](https://deepintodjango.com/prefetching-in-django). Apply the same `get_queryset` technique on the `ModelAdmin`, this cached prefetch data will also be available to your inlines.

This *should* reduce the number of queries to just a few for the entire page. But unfortunately there is a caveat...

### The bug in ForeignKeyRawIdWidget

Unfortunately, there is [currently a bug](https://code.djangoproject.com/ticket/29294#comment:2) in the Django issue tracker that prevents the widget used by`raw_id_fields` in inlines from using the prefetched values.

> **TL;DR:** in the Django code the related`ForeignKeyIdWidget` has to do a `QuerySet.get()` to get the name of the item. By doing so, the already cached prefetch\_related values are ignored. In the said bug issue I proposed a solution, but it proved to be changing too much Django code in other places as well to be accepted.

To work around this problem some code needs to be patched, a snippet with a mixin can be found lower on this page. The example below shows how to implement the patched `RawIdWidgetAdminMixin` in your code. Note that the mixin should precede ModelAdmin to work. Thanks to the mixin and `prefetch_related` your page will now consume way fewer queries!

```python
class CustomAdmin(RawIdWidgetAdminMixin, ..., ModelAdmin):
    inlines = ('some-inline',)

    def get_queryset(self, request):
        return (
            super().get_queryset(request)
            .prefetch_related('your-inline-model')
        )
```

The patched ForeignKeyRawIdWidget can be found in this Github Gist I posted below. Perhaps the most important line is 56 where an object is returned (instead of a plain value). This ultimately prevents the `get()` as on line 30, we can get the attributes from the object instead of having to query them for each item.

%[https://gist.github.com/jurrian/25a872a1c160a0e8d5a6ed8c47bf0382] 

The patch cannot be much smaller than this, as one change triggers another down the line. It can probably be smaller by monkey-patching the classes, but I prefer to keep it clean by subclassing.

### Benchmark

A change-view page with 180 inline items with relations:

1. Using no raw\_id\_fields: 1658 queries
    
2. With raw\_id\_fields and prefetching in Django 3.1: 384 queries
    
3. Patched raw\_id\_fields and prefetching: 18 queries
    

This proves that if you have a many inline items on a page, the mixin can reduce the amount of queries. Using `raw_id_fields` already drastically improves queries compared to having select-boxes. The unpatched `raw_id_fields` still produces an unacceptable high number of queries, because prefetching is not working due to the bug. This number of queries can still cause serious query times, resulting in your page to be very slow.

Maybe in the future this bug will be fixed in Django, so this patch will not be necessary anymore.