SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Views

  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Views

    I just wanted to post and let people know that I discovered views today and I love them!

    I have a really complicated query that has 6 tables joined to return only 11 columns. At present, with a small number of values to be displayed, the result was generated in 0.046 seconds. Using a view it took only 0.016 seconds. I didn't try it as a view for performance reasons I must admit, but rather because I wanted to try it out and the query I was writing was just over complicated to be using in PHP. The tables that are queried have over 250,000 records in total (a couple of 110,000 each and a few very small ones) so I thought that was a reasonable performance increase.

    I am curious however; as this is my first foray in to views, is there anything I should watch out for? Anything that may appear all shiny and great now but will bite me in the *** later on?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i am surprised you got a performance increase

    a view is nothing but the underlying query -- they should perform equally

    perhaps the rows you wanted were already sitting in buffers the second time

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    there are two ways that mysql will resolve a view: deriving the query, or using a temp table. if mysql opts for a temp table, then you can sometimes see a performance increase since the query has already been executed and the results stored. calling the view in that case simply returns the rows from the temp table instead of actually executing the query.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That might explain the varying results after I made that post! Still, I like it very much


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
  •