I am trying to optimise a query which looks like:
select prod_base.*, manu_base.name from prod_base, manu_base where
prod_base.mid=manu_base.mid;
manu_base is a table consisting of 3000 manufacturer with an id (not
unique to support synonyms) and a name (declared as varchar(32)).
prod_base is a table of products which each refer to the manufacturer id
(mid).
I have tried creating an index for manu_base using the following commands:
create index manu_mid_idx on "manu_base" using btree ("mid" "int2_ops");
drop index manu_mid_idx
create index manu_mid_idx on "manu_base" using hash ("mid" "int2_ops");
drop index manu_mid_idx
I have then run benchmarks without index, with btree and with hash, but
none seem to be faster than the other. My benchmark program is written in
c and is attached to this email. Here are the results I obtained using
time:
without index:
17.25 real 1.42 user 0.26 sys
with btree:
17.28 real 1.38 user 0.30 sys
with hash:
17.22 real 1.37 user 0.32 sys
If there is any way to make a query quicker when joining a product table
and a manufacturer table, please let me know. I've tried everything and
the results are quite fast enough.
Thanks,
Marc
