Juan E. Juan E. - 1 month ago 11
SQL Question

How can I prioritize the order of the fields in the filter

When I get the objects,

objs = Page.objects.get(slug="some-slug.html", web_id=1)


the SQL query generated is

select * from cms_page WHERE ("cms_page"."web_id" = '1' AND "cms_page"."slug" = '''some-slug''')


I would like change the SQL to

select * from cms_page WHERE ("cms_page"."slug" = '''some-slug''' and "cms_page"."web_id" = '1' )


First slug field, because the slug field is a index (Exist index with this field)

class Page(models.Model):
title = models.CharField(max_length=128)
body = models.TextField(blank=True)
slug = models.CharField(max_length=200, db_index=True)
web = models.ForeignKey(Web, editable=False)
def __unicode__(self):
return self.title
def save(self, *args, **kwargs):
if not self.slug:
self.slug = "{0}{1}".format(slugify(self.title),".html")
super(Page,self).save(*args,**kwargs)
class Meta:
unique_together = ("web", "slug")
index_together = ["web", "slug"]

Answer

For most databases, this query is exactly the same as

select * from cms_page WHERE ("cms_page"."web_id" = '1' AND "cms_page"."slug" = '''some-slug''')

This one

select * from cms_page WHERE ("cms_page"."slug" = '''some-slug''' and "cms_page"."web_id" = '1' )

You might be tempted to rewrite your queries to make arithmetic operations faster, while sacrificing readability. Because MySQL does similar optimizations automatically, you can often avoid this work, and leave the query in a more understandable and maintainable form. Some of the optimizations performed by MySQL follow:

http://dev.mysql.com/doc/refman/5.7/en/where-optimizations.html

Now, mysql isn't so hot about using indexes as postgresql is, so if mysql does, it postgresql does it too!

If an index is available, the RDBMS query parser will decide to perform the comparision on those columns that are covered by the index first.