Thread: Performance of Views

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:

SELECT
    a.orderitem_id,
    a.transaction_id,
    a.order_id,
    a.shop_id,
    a.quantity,
    a.price,
    b.affiliate_id,
    c."type"
FROM
    ss_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) AND
    a.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

Steffen Boehme <Steffen@boemm.de> writes:

> FROM
>     ss_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) AND

What is that last line doing there? It's completely redundant and could very
well be the source of your problems.

For useful help you should post the \d output for the three tables and the
result of "EXPLAIN ANALYZE SELECT ...".

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

A view doesn't change performance at all. It's exactly the same as writing the
query in the view directly into your query.

--
greg