#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    28
    Rep Power
    0
    Firstly I'm new to MySQL & PHP, secondly this topic was partially covered elsewhere but my question wasn't answered (so please don't flame me )

    I've got a DB for news with DateTime as the Primary Key. On the homepage I have the 7 newest story headlines displayed, with a brief 2 line synopsis and link to the full story.

    I have all this working fine using:
    SELECT date, headline, synopsis FROM story ORDER BY date DESC

    I then loop through the first 7 results. However I'm wondering just how efficient this is. The table is small at the moment but will get bigger and bigger with potentially 1000s of stories. What kind of performance hit can I expect as the DB grows?

    I also have another table for feature stories which does exactly the same thing on the homepage, ie display 3 newest features.

    Is there a more efficient way of doing this, something like explicitly selecting the last 7 rows ordered by date, without having to select every row in the table?

    Thanks in advance for any advice/help.

    [This message has been edited by Sidane (edited June 16, 2000).]
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Limit the result to 7 or however many you want.

    SELECT date, headline, synopsis FROM story ORDER BY date DESC limit 7
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    28
    Rep Power
    0
    Would somebody please tell me why I didn't think of that in the first place!!

    I'm sure I tried that and something went wrong. But it works fine now. Cheers.

    What about the performance issue of searching through ever record in the table? Does the LIMIT parameter avoid this?
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    No, because MySQL still has to read all the records to order them. It does save on your script processing time and system memory.

Similar Threads

  1. SELECTing the 'middle' row in a table
    By C3P0 in forum MySQL Help
    Replies: 4
    Last Post: November 12th, 2003, 02:22 PM
  2. SELECT DISTINCT returns all rows in table
    By C3P0 in forum MySQL Help
    Replies: 5
    Last Post: October 22nd, 2003, 05:05 AM
  3. Count amount of rows in same query of selecting rows
    By JasonL in forum PHP Development
    Replies: 2
    Last Post: October 15th, 2003, 11:04 AM
  4. Copying table rows quickly?
    By chakotha in forum MySQL Help
    Replies: 3
    Last Post: October 3rd, 2003, 04:27 PM
  5. How can I create links in table rows?
    By jkoerber in forum HTML Programming
    Replies: 2
    Last Post: June 1st, 2003, 06:56 PM

IMN logo majestic logo threadwatch logo seochat tools logo