SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL View: recommended algorithm?

    Hi all,

    I was wondering if you could explain a little more what is the best choice for a view algorithm. Reading on Google and MySQL Dev site did not helped me to clear my concerns, so i was hoping one expert could enlighten me.

    The scenario is simple:
    I create a view that pulls data from 5 columns, some of them being JOINed from other tables:
    Code:
    SELECT thread.threadid, thread.threadtitle, post.postid, forum.forumtitle, user.username
    FROM thread
    LEFT JOIN post ON (thread.threadid = post.threadid)
    LEFT JOIN user ON (thread.userid = user.userid)
    LEFT JOIN forum ON (thread.threadid = forum.threadid)
    Then I run a simple view:
    Code:
    SELECT * FROM view WHERE dateline < 84600
    I have indexes on threadid, postid and forumid columns.
    What algorithm you would use for the above case? UNDEFINED, MERGE or TEMPTABLE? Could you you please explain why also? I currently use TEMPTABLE, because the view is used into a cronjob that is performed every 10min. The idea is to release the table locks as soon as possible, so TEMPTABLE (from my understanding) will create a temporary table with the view data, then release then locks and push the SQL data. Do you think using TEMPTABLE could affect the server load? Is it better to simply use UNDEFINED?

    Thanks for your explanations.

  2. #2
    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)
    generally, if any table involved in a view gets frequent updates then the view should be MERGE and not TEMPTABLE. the amount of time saved by releasing locks early with TEMPTABLE is probably smaller than the amount of time it takes to create the temporary table.

    this can be mitigated by a two layer view if, for example, only posts and users are updated frequently but thread and forum are not:
    Code:
    create algorithm = temptable
      view _view_threads_inside
    select ...
      from thread
    left outer
      join forum
        on ...;
    
    create algorithm merge
      view view_threads
    select ...
      from _view_threads_inside
    left outer
      join user
        on ...
    left outer
      join post
        on ...;
    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

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the nice explanation. longneck.
    The problem I need to deal is this:
    Users post actively on a very large board. The data is stored first into post table (forum table gets updated with the latestpost column also), then thread table gets updated and finally... user table have a minimal change (statistics). This is independent of my view, it is handled by the default software. However, I want to pull (on the side, for personal usage) some of the data, from the above mentioned tables. We are talking about one user doing a view query every 10 minutes.

    Do I still follow your advice (multiple layers)? Thanks for your patience.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Teckinno View Post
    We are talking about one user doing a view query every 10 minutes.
    just run the query, never mind the views, temp tables, merges, heaps, stacks, piles, or whatever...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aha, I thought a view is more performant, compared to a direct (large) query?
    The idea is to also learn how to use properly the algorithms.

  6. #6
    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)
    in your case, a view will not improve performance. performance is just one small advantage of views.

    the biggest win of views is abstraction. you can hide the implementation of your daa behind a single statement. this helps guard against dependency on specific table structures; if the tables need to change, then you only need to update the views that reference those tables and not all your code. you can also secure your data on a row-by-row basis at the server by including current_user() in a where clause somewhere.
    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

  7. #7
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the clarification.


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
  •