Performance of Views - Mailing list pgsql-sql

From Steffen Boehme
Subject Performance of Views
Date
Msg-id 38h2r6F5k5o1dU1@individual.net
Whole thread Raw
List pgsql-sql
Hello there,

i have a short question ...

I have a few tables (at the moment "only" 3 for testing), over which
will by made a query in this form:

SELECTa.orderitem_id,a.transaction_id,a.order_id,a.shop_id,a.quantity,a.price,b.affiliate_id,c."type"
FROMss_order_orderitems a      LEFT JOIN ss_order_affiliate_tracking b ON a.order_id = b.order_id,
ss_shops c
WHERE(a.order_id = b.order_id OR b.order_id IS NULL) ANDa.shop_id = c.shop_id;

The query will get later a few more conditions ...

Now is the problem, that the performance is not realy good ... and i
think about the best solution for such a query and found three possibilitys:

1. a simple select over the three tables (one of them contains 160000
entrys in the moment and it's growing) in the form like above (the db is
mysql 4.1.x)
I think this solution is not very perfomant ...

2. move the data to a postgresql-db with the same structur and create a
view, wich makes the same query ...
Is the performance for the same query different between a simple select
and a view!?
If so, i can forget the view ...

3. put the data with the above query in one big table ...
I know, thats no good db-structur, but i don't know how i could make it
better ...


The main-question at the moment iss ...
Is the performance of the View-Method better then the first Method on
the existing tables!?

I hope of a view hints ...

Thanks
Steffen


pgsql-sql by date:

Previous
From: Josh Berkus
Date:
Subject: Re: crosstab
Next
From: T E Schmitz
Date:
Subject: psql encoding problem