Welcome to Geeklog Wednesday, September 30 2020 @ 02:51 am EDT

Geeklog Forums

How to get the latest articles?


Status: offline

OMAL

Forum User
Regular Poster
Registered: 06/12/17
Posts: 102
I'm embedding a static page to the toppage, that is based on phpblock_lastarticles.php released by the japanese geeklog package.
I'm using the following sql to get latest articles.
PHP Formatted Code
$sql = "SELECT s.sid, t.tid, s.title AS title, s.date, s.group_id, s.owner_id, s.perm_owner, s.perm_group, s.perm_members, s.perm_anon, t.topic, s.introtext ,s.bodytext, '0' as cid
                FROM {$_TABLES['stories']} AS s, {$_TABLES['topic_assignments']} AS a, {$_TABLES['topics']} AS t
                WHERE a.type = 'article'
                AND s.title <> ''
                AND (s.sid = a.id)
                AND (s.draft_flag =0)
                AND (s.date <=CURRENT_TIMESTAMP)
                AND (s.featured =0)
                AND (a.tid = t.tid)
                order by date desc LIMIT {$offset} , {$numrows}
                "
;

The performance of this sql is not enough to deal with a website having about 20,000 stories. It takes around 1 minutes for the first run of the sql, and the sql is cached by the server so that it takes few seconds after seconds run while it has been cached.

Speaking of getting latest articles, the geeklog index.php does that. So my question is, are there any difference between the sql above and the sql used in index.php?

If it is faster in index.php, I'll appreciate it, but from what seen, I can't find the exact sql used to show the top page in geeklog.

Thanks.
 Quote

Status: offline

Laugh

Site Admin
Admin
Registered: 27/09/05
Posts: 1401
There is a few differences. Here is the sql generated for anonymous users for the articles on the homepage from index.php:


PHP Formatted Code

SELECT s.*, UNIX_TIMESTAMP(s.date) AS unixdate, UNIX_TIMESTAMP(s.expire) as expireunix, u.uid, u.username, u.fullname, u.photo
FROM gl_stories AS s, gl_topic_assignments AS ta, gl_users AS u, gl_topics AS t
WHERE (s.uid = u.uid) AND (ta.tid = t.tid) AND ta.type = 'article' AND ta.id = s.sid AND (date <= NOW()) AND (draft_flag = 0) AND frontpage = 1 AND ta.tdefault = 1 AND (s.perm_anon >= 2) AND (ta.tid IN ('General','Geeklog'))
GROUP BY s.sid, s.uid, s.draft_flag, s.date, s.modified, s.title, s.page_title, s.introtext, s.bodytext, s.text_version, s.hits, s.numemails, s.comments, s.comment_expire, s.trackbacks, s.related, s.featured, s.show_topic_icon, s.commentcode, s.structured_data_type, s.trackbackcode, s.statuscode, s.expire, s.postmode, s.advanced_editor_mode, s.frontpage, s.meta_description, s.meta_keywords, s.cache_time, s.owner_id, s.group_id, s.perm_owner, s.perm_group, s.perm_members, s.perm_anon, expireunix, u.uid, u.username, u.fullname, u.photo, date
ORDER BY featured DESC, date DESC LIMIT 0, 6
 


It may be a bit easier to follow but here is the sql from lib-common.php used by the function COM_whatsNewBlock to create the list of articles for the What's New Block.

PHP Formatted Code

SELECT sid, title
FROM gl_stories, gl_topic_assignments ta
WHERE (date >= (date_sub(NOW(), INTERVAL 86400 SECOND))) AND (date <= NOW()) AND (draft_flag = 0) AND ta.type = 'article' AND ta.id = sid AND (perm_anon >= 2) AND (ta.tid IN ('General','Geeklog'))
GROUP BY sid, title, date
ORDER BY date DESC
 


Remember the sql statement for this does depend on what topics the user (which is anonymous on my Development Server) has access to. You also could have articles belong to multiple topics (which is the reason why you need the group by clause).

The main thing to speed up things that you might be missing are indexes on the SQL tables in your database. Take a look at the sql tables involved in the sql statements and then take a look at the creation of those tables in mysql_tableanddata.php file. Indexes on tables can make a HUGE difference in speed.
One of the Geeklog Core Developers.
 Quote

Status: offline

OMAL

Forum User
Regular Poster
Registered: 06/12/17
Posts: 102
Thanks Laugh. I'll try it later.
 Quote

All times are EDT. The time is now 02:51 am.

  • Normal Topic
  • Sticky Topic
  • Locked Topic
  • New Post
  • Sticky Topic W/ New Post
  • Locked Topic W/ New Post
  •  View Anonymous Posts
  •  Able to post
  •  Filtered HTML Allowed
  •  Censored Content