Views Performance

Hello everybody..

I am developing a large database site. And expecting millions of data
and millions of user will visit site. As there are lots of dependent
tables so I need to create views for that or I can use Join Query.

Now main problem is that I am very confused regarding performance
issue of Views and Join query. There might be possible that there are
lots of complex join queries required to fetch result. So Can anyone
suggest me Which one is more efficient and faster in data fetching.

I am using MySQL as database.

Any help in welcome for me. I need it urgent. So please help me ASAP.

Regards,
Mitul Patel.

On 21 Feb, 13:24, "Mitul" <patel.mitu...@gmail.com> wrote:
> Hello everybody..
>
> I am developing a large database site. And expecting millions of data
> and millions of user will visit site. As there are lots of dependent
> tables so I need to create views for that or I can use Join Query.
>
> Now main problem is that I am very confused regarding performance
> issue of Views and Join query. There might be possible that there are
> lots of complex join queries required to fetch result. So Can anyone
> suggest me Which one is more efficient and faster in data fetching.
>
> I am using MySQL as database.
>
> Any help in welcome for me. I need it urgent. So please help me ASAP.
>
> Regards,
> Mitul Patel.

In general fresh joins are more efficient. A view doesn't know what
may happen on the outside of it. Views used in other queries are
effectively sub-selects and they are not, in general as efficient as
pure joins.

On 21.02.2007 14:37, Captain Paralytic wrote:
> On 21 Feb, 13:24, "Mitul" <patel.mitu...@gmail.com> wrote:
>> I am developing a large database site. And expecting millions of data
>> and millions of user will visit site. As there are lots of dependent
>> tables so I need to create views for that or I can use Join Query.
>>
>> Now main problem is that I am very confused regarding performance
>> issue of Views and Join query. There might be possible that there are
>> lots of complex join queries required to fetch result. So Can anyone
>> suggest me Which one is more efficient and faster in data fetching.
>>
>> I am using MySQL as database.
>>
>> Any help in welcome for me. I need it urgent. So please help me ASAP.
>
> In general fresh joins are more efficient. A view doesn't know what
> may happen on the outside of it. Views used in other queries are
> effectively sub-selects and they are not, in general as efficient as
> pure joins.

Hm... I don't know what you mean by "A view doesn't know what may
happen on the outside of it". Can you elaborate on that?

Generally a view is as fast as using the view's definition directly. If
you do not need all tables joined that are joined in a view then yes,
performance characteristics can be different (likely faster). But
that's an unfair comparison because you compare apples and oranges (i.e.
different queries).

Or does MySQL treat views differently than other RDBMS? I'd be
surprised to hear that and IMHO it would limit usability of views in
MySQL quite a bit.

Kind regards

robert

On 21 Feb, 14:09, Robert Klemme <shortcut...@googlemail.com> wrote:
> On 21.02.2007 14:37, Captain Paralytic wrote:
>
>
>
>
>
> > On 21 Feb, 13:24, "Mitul" <patel.mitu...@gmail.com> wrote:
> >> I am developing a large database site. And expecting millions of data
> >> and millions of user will visit site. As there are lots of dependent
> >> tables so I need to create views for that or I can use Join Query.
>
> >> Now main problem is that I am very confused regarding performance
> >> issue of Views and Join query. There might be possible that there are
> >> lots of complex join queries required to fetch result. So Can anyone
> >> suggest me Which one is more efficient and faster in data fetching.
>
> >> I am using MySQL as database.
>
> >> Any help in welcome for me. I need it urgent. So please help me ASAP.
>
> > In general fresh joins are more efficient. A view doesn't know what
> > may happen on the outside of it. Views used in other queries are
> > effectively sub-selects and they are not, in general as efficient as
> > pure joins.
>
> Hm... I don't know what you mean by "A view doesn't know what may
> happen on the outside of it". Can you elaborate on that?
Yeah, I wasn't too clear (but then neither was the OP)!
The OP said "As there are lots of dependent tables so I need to create
views for that or I can use Join Query", as if a view would not
contain a join query. I was meaning that, if the OP was gong to use
views in another join query, the view would not be able to undergo any
further optimisation which would be available by taking the coplete
joined query as a whole.

On 21.02.2007 15:51, Captain Paralytic wrote:
> On 21 Feb, 14:09, Robert Klemme <shortcut...@googlemail.com> wrote:
>> On 21.02.2007 14:37, Captain Paralytic wrote:
>>> On 21 Feb, 13:24, "Mitul" <patel.mitu...@gmail.com> wrote:
>>>> I am developing a large database site. And expecting millions of data
>>>> and millions of user will visit site. As there are lots of dependent
>>>> tables so I need to create views for that or I can use Join Query.
>>>> Now main problem is that I am very confused regarding performance
>>>> issue of Views and Join query. There might be possible that there are
>>>> lots of complex join queries required to fetch result. So Can anyone
>>>> suggest me Which one is more efficient and faster in data fetching.
>>>> I am using MySQL as database.
>>>> Any help in welcome for me. I need it urgent. So please help me ASAP.
>>> In general fresh joins are more efficient. A view doesn't know what
>>> may happen on the outside of it. Views used in other queries are
>>> effectively sub-selects and they are not, in general as efficient as
>>> pure joins.
>> Hm... I don't know what you mean by "A view doesn't know what may
>> happen on the outside of it". Can you elaborate on that?
> Yeah, I wasn't too clear (but then neither was the OP)!
> The OP said "As there are lots of dependent tables so I need to create
> views for that or I can use Join Query", as if a view would not
> contain a join query. I was meaning that, if the OP was gong to use
> views in another join query, the view would not be able to undergo any
> further optimisation which would be available by taking the coplete
> joined query as a whole.

Are you referencing to the database's optimization or manual
optimization? I can't really believe that MySQL would not be able to
properly optimize a query that contains a view (see my other posting).
If you refer to the latter, then yes ("optimizing" in this case meaning
"remove unnecessary tables from the join").

Kind regards

robert

On 21 Feb, 15:18, Robert Klemme <shortcut...@googlemail.com> wrote:
> On 21.02.2007 15:51, Captain Paralytic wrote:
> Are you referencing to the database's optimization or manual
> optimization? I can't really believe that MySQL would not be able to
> properly optimize a query that contains a view (see my other posting).
Yep, that's what I'm referring to.
A view appears as a table, as does a sub-select. Sub-selects are a
bugger for optimisation as admitted in the MySQL manual.

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in
news:1172071406.444639.38610@j27g2000cwj.googlegro ups.com:

> A view appears as a table

I beg to differ (at least on 5.0.24).

The following code:

USE test;
DROP TABLE IF EXISTS foo, bar, more;

CREATE TABLE foo (
id INT UNSIGNED NOT NULL PRIMARY KEY
);

CREATE TABLE bar (
id INT UNSIGNED NOT NULL PRIMARY KEY,
fid INT UNSIGNED NOT NULL
);

CREATE OR REPLACE VIEW foobar AS
SELECT foo.id AS fid, bar.id AS bid
FROM foo
JOIN bar ON bar.fid = foo.id;

CREATE TABLE more (
id INT UNSIGNED NOT NULL PRIMARY KEY,
fid INT UNSIGNED NOT NULL,
bid INT UNSIGNED NOT NULL
);

EXPLAIN
SELECT m.id, fb.fid, fb.bid
FROM more m
JOIN foobar fb ON fb.fid = m.fid AND fb.bid = m.bid \G


outputs:

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: m
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: foo
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.m.fid
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: bar
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.m.bid
rows: 1
Extra: Using where
3 rows in set (0.00 sec)

mysql>



--
felix

Hello friend,

Sorry for confussion. As I am talking about Performance of View and
Query contains Join and subquery.

Here is more clear explination....

For Example There is view whose defination contains 6-7 joins and 5-6
subquiries. And same Query but not treating as View. Which one is
faster? There will be a case that We can apply filter in Query.

Regards,
~~Mitz~~

On 21.02.2007 19:28, Mitul wrote:
> Sorry for confussion. As I am talking about Performance of View and
> Query contains Join and subquery.
>
> Here is more clear explination....
>
> For Example There is view whose defination contains 6-7 joins and 5-6
> subquiries. And same Query but not treating as View. Which one is
> faster? There will be a case that We can apply filter in Query.

With no additional filters both variants should perform equally on any
decent RDBMS. With additional filters it depends where you apply them.

robert