Pensamento rápido – “ilike” SQL

4 Sep

Today I saw a post at one of the blogs I often watch, where a guy said he got 1000% of speed boost in a aplication by changing: uma aplicação trocando um:

SELECT * FROM table WHERE fieldname ILIKE 'abc%';

For

SELECT * FROM table WHERE lower(fieldname) LIKE 'abc%';

(changed ilike to lower … like)
Kinda ‘newbie’ (hehehe), obviously this absurd improve happened in a giant data mass and probably in a field without appropriated index.
BTW, my tip is other:
Ilike isn’t supported for mysql and sqlite. So use it its extremely inappropriate, especially if you are working with Django, Rails or any application layer that provides a multi-db.
Sure the way to make it happens with the above frameworks is “forcing” ilike.Their ORM are pretty much able to handle this comparisons.

Conclusion
Avoid “ilike” on sql statements, use “lower()… like” or “upper… like”; And remember to change variables to lower/upper case!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

One Response to “Pensamento rápido – “ilike” SQL”

  1. Gnomozarp October 29, 2009 at %I:%M %p #

    Xuxa, só pra complementar… eu fiz um teste aqui no MS-SQL e fica até mais lento colocar o upper ou lower… mas algo em torno de 1% 🙂
    Gnomozarp

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)

Leave a Reply