SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Performance of MySQL Views

    I have a table with about 40,000 records. About 100 random records get updated every second.

    One of the most frequent queries on that table is getting a the last 30 updated records. (i get last updated by checking a timestamp, which is one of the fields for each record)

    I just found out about MySQL views, and was wondering if I can use views to optimize this. Basically my plan is to create a view of, well, "Select * from base_table order by time_stamp desc limit 30".

    My question is: will this improve (or decrease, or make no impact on) performance at all? Maintaining the view must have a performance impact too. How/When does the view get updated? (i.e. when the base table is updated? or when the view is accessed?)

    Thanks for any input!

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/doc/refman/5.0/...lgorithms.html

    A view, at least in MySQL, is basically just an alias for a saved SELECT query. By default it's literally not any kind of object, referencing the view just results in MySQL rewriting your query to include the query you used to define the view.

    So it won't help or hurt you.

    With only 40,000 records, add an index on the timestamp column and you'll have no problems querying for the last 30 rows as often as you want.

    Other RDBMS's support more advanced types of views, MySQL is pretty basic.


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
  •