SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Performance question

    I am making something that will need to show the last post date. So would you have a field in the db that holds the last post date that you update on every post, or would you just use a query to get the last date? Opinions please!

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    just get the last date

    chances are, you will probably have at least one query that will want to return posts in descending order (like most weblogs), so you'll have an index on that date field

    select max(datefield) will be blindingly fast, and you save yourself a needless update too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Problem is mysql can't use subqueries so you'll either have to use temp tables or join goofily or have more than one query.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you're right, matt, but where did you catch that (a) this was mysql, and (b) anything other than the last post date was required?

    if it is mysql, and if details for rows qualified by a subselect are required, then i'd probably go with two consecutive queries

    rudy

  5. #5
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    He's right! My question really was would it be quicker updating a field after every post or running two SELECTs?

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,286
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    In my mind, it would depend on a couple of factors:

    1) the volume of database action. This can be a double edged sword. If you are inserting/updating a table a lot, then it can get cumbersome doing an extra update everytime and might want to consider doing the max. On the other hand, if need to pull information from a number of tables at the same time, then you may want to consider adding the last update date to a less active table.
    2) database platform. Like he said, MySQL 3.x doesn't support sub-selects (I think v4 will/does - don't know if it's been released yet)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


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
  •