1

I have the current query below where email addresses in different tables are in various cases so I need to compare lowercase to lowercase. But running the query below with lower() is something like 100 million times slower than running it without.

Can anyone suggest a workaround?

SELECT 
  person_oct.email, 
  unsubs.unsubs_email
FROM 
  public.unsubs, 
  public.person_oct
WHERE 
  lower(person_oct.email) = lower(unsubs.unsubs_email) AND
  unsubs.unsubs_email IS NOT NULL  AND 
  unsubs.unsubs_email != '' AND 
  person_oct.email != '' AND 
  person_oct.email IS NOT NULL ;
CoSpringsGuy
  • 1,615
  • 1
  • 13
  • 16
Jonathan Crow
  • 11
  • 2
  • 2
    Which DBMS are you using? And what is the complete execution plan for that query with and without the lower()? You should be able to improve that by adding an index on `lower()` for those two columns –  Nov 09 '16 at 22:17
  • try this solution http://stackoverflow.com/questions/1224364/sql-server-ignore-case-in-a-where-expression – Sparrow Nov 09 '16 at 22:19
  • 1
    string manipulation can block the use of index .. – ScaisEdge Nov 09 '16 at 22:26
  • 1
    In SQL Server, switch the columns to a non-case-sensitive collation (and omit the calls to `lower`) – Blorgbeard Nov 10 '16 at 00:36

1 Answers1

0

In many SQL servers string comparison is case insensitive by default. You might not need the lower() calls.

You also don’t need the last two lines of your query as they are implicit in the first three lines of the where-block.

Jakob Stoeck
  • 624
  • 4
  • 12
  • "Many" is an exaggeration. Postgres, DB2, Oracle, Firebird, Ingres, Informix, Vertica, Teradata all do case-sensitve string comparison by default. I think it's only SQL Server (and its cousin Sybase) and MySQL that don't –  Nov 10 '16 at 06:48
  • I am using POSTGRES and it made a difference. I wound up just changing the column email=upper(email). A bit of a cheat but totally made the difference. – Jonathan Crow Dec 05 '16 at 18:04