3 ms·
not sure how converting a WHERE to a JOIN helps when you're doing adhoc queries like this to look up records: `SELECT * FROM users WHERE email LIKE 'FirstName@
by parpfish 2y ago
not sure how converting a WHERE to a JOIN helps when you're doing adhoc queries like this to look up records:
`SELECT * FROM users WHERE email LIKE 'FirstName@domain.net'`
- sgarland 2y agoI misunderstood your point; I thought you were saying having a surrogate key here would be better. For the above, either ensure that all email addresses are stored in one case (and index / query on the same), or if that’s impossible for some reason, you can query with ILIKE, optionally creating a trigram index (for Postgres; this isn’t a problem in MySQL since by default it’s entirely case-insensitive) on the column. Another option is to index with a functional index (CREATE INDEX user_email_lowercase ON user (LOWER(email))), and then code the API doing these lookups to cast to lowercase. That way, it’ll be retrieved and displayed however the user entered it, but retrieved based only on the CI version.