Hi,
I'm using 7.0.3 and would like to know if there is a way to optimize
... where col1 LIKE "%" ...
with LIKE "%"
select h.user_id as hotel_id, h.m1_sterne as sterne, h.m1_hotel as
hotel, h.m1_ort as ort, h.m1_plz as plz, h.m1_region as region,
sum(k.preis * 1) as preis from hotels h, best_EZ k where h.user_id =
k.hotel_id and k.datum between '2001-02-01'::date and '2001-02-15'::date
- 1 and k.datum>now() and k.menge - k.reserviert - k.gebucht>=1 and
h.m1_region like 'Deutschland %' and h.m1_plz like '%' and h.m1_ort like
'%' and h.m1_sterne like '%' group by h.user_id, h.m1_hotel,
h.m1_sterne, h.m1_ort, h.m1_plz, h.m1_region, h.user_id having
count(*)>=14;
without LIKE "%"
select h.user_id as hotel_id, h.m1_sterne as sterne, h.m1_hotel as
hotel, h.m1_ort as ort, h.m1_plz as plz, h.m1_region as region,
sum(k.preis * 1) as preis from hotels h, best_EZ k where h.user_id =
k.hotel_id and k.datum between '2001-02-01'::date and '2001-02-15'::date
- 1 and k.datum>now() and k.menge - k.reserviert - k.gebucht>=1 and
h.m1_region like 'Deutschland %' group by h.user_id, h.m1_hotel,
h.m1_sterne, h.m1_ort, h.m1_plz, h.m1_region, h.user_id having
count(*)>=14;
Although the two queries should be equivalent the second takes about 4s
and the first takes almost 2 MINUTES.
I optimized this query manually by recognizing the special cases in my
program, but this shouldn't happen.
--
Nabil Sayegh
GPG-Key available at http://www.sayegh.de
(see http://www.gnupg.org for details)