Opened 18 years ago

Last modified 11 years ago

#3575 closed

[patch] iexact searches should use LOWER() not ILIKE — at Initial Version

Reported by: Jack Moffitt <metajack@…> Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: metajack@…, tofu@…, sam@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

Currently queries using fieldiexact="blah" translate to field ILIKE 'blah'. This is really inefficient because ILIKE queries (since they are for pattern matching) cannot be indexed and the whole table must be scanned. If LOWER(field) = LOWER('blah') is used, it accomplishes the same thing, but an index can be created for LOWER(field) that eliminates the table scan.

This is a little tricky since the default substitution is only on the operator and the right operand, but this patch adds a column substitution array as well. This patch should probably be applied across all backends where LOWER() is supported. Currently it only changes the postgresql_psycopg2 backend.

Change History (1)

by Jack Moffitt <metajack@…>, 18 years ago

Attachment: django_db.diff​ added

patch to make iexact searches use LOWER() instead of ILIKE

Note: See TracTickets for help on using tickets.
Back to Top