June 16th, 2000, 06:26 AM
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).]
June 16th, 2000, 08:06 AM
Limit the result to 7 or however many you want.
SELECT date, headline, synopsis FROM story ORDER BY date DESC limit 7
June 16th, 2000, 08:37 AM
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?
June 16th, 2000, 12:39 PM
No, because MySQL still has to read all the records to order them. It does save on your script processing time and system memory.