Lets say I have the following model
name = models.CharField(max_length=20, primary_key=True)
all_alex = Person.objects.filter(name__startswith='alex')
all_white = Person.objects.filter(name__endswith='white')
Yes, your understanding is correct.
select * from foo where bar like 'text1%' and bar like '%text2'
is not necessarily optimal. This could be an improvement:
select * from (select * from foo where foo.bar like 'text1%') t where t.bar like '%text2'
You need to make measurements to check whether this is better. If it is, the cause is thatin the inner query you use an index, while in the outer query you not use an index, but the set is prefiltered by the first query, therefore you have a much smaller set to query.
I am not at all a Django expert, so my answer might be wrong, but I believe chaining your
filter would be helpful if
filter actually executes the query. If that is the case, then you can use the optimization described above. If
filter just prepares a query and chaining filters will result in a single query different from the one above, then I recommend using hand-written MySQL. However, if you do not have performance issues yet, then it is premature to optimize it, since you cannot really test the amount of performance you gained.