SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I need your help to "Optimize" my tables and queries

    Hello,

    I'm writting my own forum scripts, something like the Sitepoint Forums. I know that there are a lot of forum scripts out there but it's like a challenge to me. I want to know if I can really do it.

    What I've created so far is working, but I need you guys to take a look at my tables and queries and let me know if they are "optimized". Is there a better way to do it? I would really appreciate your feedback.

    I have a table that contains the forum Categories:

    Code:
    catid     - INT(10) UNSIGNED PRIMARY_KEY AUTO_INCREMENT NOT_NULL
    category  - VARCHAR(50) NOT_NULL
    sortorder - INT(10) INDEX UNSIGNED NOT_NULL
    a table that contains the Forums:

    Code:
    forumid   - INT(10) UNSIGNED PRIMARY_KEY AUTO_INCREMENT NOT_NULL
    catid     - INT(10) INDEX UNSIGNED NOT_NULL
    forum     - VARCHAR(50) NOT_NULL
    descript  - VARCHAR(250) NOT_NULL
    topics    - INT(10) UNSIGNED NOT_NULL (total topics in the forum)
    posts     - INT(10) UNSIGNED NOT_NULL (total post in the forum)
    sortorder - INT(10) INDEX UNSIGNED NOT_NULL
    lastpost  - INT(10) UNSIGNED NOT_NULL (id of last posted post in the forum)
    a table that contains the forum Topics:

    Code:
    topicid   - INT(10) UNSIGNED PRIMARY_KEY AUTO_INCREMENT NOT_NULL
    forumid   - INT(10) INDEX UNSIGNED NOT_NULL
    firstpost - INT(10) UNSIGNED NOT_NULL (id of first posted post in the topic)
    lastpost  - INT(10) UNSIGNED NOT_NULL (id of last posted post in the topic)
    replies   - INT(10) UNSIGNED NOT_NULL (total replies in the topic)
    views     - INT(10) UNSIGNED NOT_NULL (total views of the topic)
    a table that contains the topic Posts:

    Code:
    postid   - INT(10) UNSIGNED PRIMARY_KEY AUTO_INCREMENT NOT_NULL
    topicid  - INT(10) INDEX UNSIGNED NOT_NULL
    forumid  - INT(10) INDEX UNSIGNED NOT_NULL
    subject  - VARCHAR(100) NOT_NULL
    message  - TEXT NOT_NULL
    username - VARCHAR(25) NOT_NULL
    userid   - INT(10) UNSIGNED NOT_NULL
    datetime - DATETIME NOT_NULL
    ipaddr   - VARCHAR(25) NOT_NULL
    and a table that contains the Users:

    Code:
    userid   - INT(10) UNSIGNED PRIMARY_KEY AUTO_INCREMENT NOT_NULL
    username - VARCHAR(25) NOT_NULL
    password - VARCHAR(25) BINARY NOT_NULL
    name     - VARCHAR(50) NOT_NULL
    email    - VARCHAR(50) NOT_NULL
    birth    - DATE NOT_NULL
    gender   - ENUM('','male','female') DEFAULT('') NOT_NULL
    location - VARCHAR(50) NOT_NULL
    website  - VARCHAR(50) NOT_NULL
    posts    - INT(10) UNSIGNED NOT_NULL (total post made by user)
    joined   - DATE NOT_NULL
    lastpost - TIMESTAMP(8) (date of last post made by user)
    I use the following query to display all the forums:

    Code:
    $result = @mysql_query("SELECT
    c.catid,c.category,f.forumid,f.forum,f.descript,f.topics,f.posts,p.username,date_format(p.datetime,'%b %e, %Y %H:%i') as datetime
    FROM Categories c LEFT JOIN Forums f ON (f.catid=c.catid) LEFT JOIN Posts p ON (p.postid=f.lastpost)
    ORDER BY c.sortorder,f.sortorder ASC");
    Some categories might not contain any forums, but I would still like to display the category. That is the reason why I use the LEFT join here instead of the INNER join.

    I use the following query to display all topics in a forum:

    Code:
    $result = @mysql_query("SELECT
    t.topicid,t.replies,t.views,p.subject,p.username,p.userid,q.username as lastuser,date_format(q.datetime,'%b %e, %Y %H:%i') as datetime2
    FROM Topics t INNER JOIN Posts p ON (p.postid=t.firstpost) INNER JOIN Posts q ON (q.postid=t.lastpost)
    WHERE t.forumid='$forumid'
    ORDER BY p.datetime DESC");
    What bothers me in the query above is that I have to do an Inner join on the table Posts twice to get the info I need, first to get the info of the first post and then again to get the info of the last post.

    And last but not least I use the following query to display all post in a topic:

    Code:
    $result = @mysql_query("SELECT
    p.postid,p.ipaddr,p.subject,p.message,p.username,p.userid,date_format(p.datetime,'%b %e, %Y %H:%i') as datetime2,u.posts,date_format(u.joined,'%b %Y') as joined
    FROM Posts p LEFT JOIN Users u ON (u.userid=p.userid)
    WHERE p.topicid='$topicid'
    ORDER BY p.datetime ASC LIMIT");
    I use LEFT join here because guests are also allowed to post.

    So what do you guys think?
    Ballot-Box.net - free polls for webmasters
    FormLog.com - free form processor

  2. #2
    SitePoint Guru DeNasio's Avatar
    Join Date
    May 2001
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No one?

    I really need your help guys.
    Ballot-Box.net - free polls for webmasters
    FormLog.com - free form processor


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
  •