Professional Web Applications Themes

Views Performance - MySQL

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 ...

  1. #1

    Default 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.

    Mitul Guest

  2. #2

    Default Re: Views Performance

    On 21 Feb, 13:24, "Mitul" <com> wrote: 

    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.

    Captain Guest

  3. #3

    Default Re: Views Performance

    On 21.02.2007 14:37, Captain Paralytic wrote: 
    >
    > 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.[/ref]

    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
    Robert Guest

  4. #4

    Default Re: Views Performance

    On 21 Feb, 14:09, Robert Klemme <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > 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?[/ref]
    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.

    Captain Guest

  5. #5

    Default Re: Views Performance

    On 21.02.2007 15:51, Captain Paralytic wrote: 
    >> 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?[/ref]
    > 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.[/ref]

    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
    Robert Guest

  6. #6

    Default Re: Views Performance

    On 21 Feb, 15:18, Robert Klemme <com> wrote: 
    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 Guest

  7. #7

    Default Re: Views Performance

    "Captain Paralytic" <com> wrote in
    news:googlegroups.com:
     

    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
    Felix Guest

  8. #8

    Default Re: Views Performance

    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~~

    Mitul Guest

  9. #9

    Default Re: Views Performance

    On 21.02.2007 19:28, Mitul wrote: 

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

    robert
    Robert Guest

Similar Threads

  1. views in 8.0
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 11th, 01:26 PM
  2. performance tools for yzing java performance
    By dfdashk@unix.com in forum Sun Solaris
    Replies: 6
    Last Post: August 28th, 03:40 PM
  3. Views or WHERE
    By Ray in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 18th, 12:38 PM
  4. Replies: 4
    Last Post: July 8th, 07:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139