SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Dec 2002
    Location
    Wisconsin
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimizing speed through query minimization

    I'm interested in making the forum I'm building as fast as possible, and from the research I've done around here, minimizing queries is one step to help.

    What counts as a query? I saw a page from a future competitor, and the bottom of the page said there were 22 queries, but I can't see how it took 22.


    Thanks,


    Jeff

  2. #2
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been interested in this for a little while now...often when looking at vBullitin pages, I try to figure out how many queries would need to be done.

    22 seems like a lot to me. I would think that with good database design, you should only have to use one query for each kind of data you are recovering.

    I really don't know how vBullitin (for example) works, but this rolls off the top of my head:
    1. The current viewing user's preferences are discovered or validated.
    2. Thread details are determined (what posts are in the thread, is the thread open or closed, etc).
    3. The posts are gathered.
    4. Information about the users who made each post is gathered (avatar location, current signature, etc.)

    I suppose that when faced with the actual tasks at hand, it would be possible to use more than one query for each of those. Then there may be other database driven details too. I don't know, 22 sounds high. Then again, I've never attempted to make a forum, so beats me!
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  3. #3
    SitePoint Addict
    Join Date
    Dec 2002
    Location
    Wisconsin
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seemed high to me, too. Especially since the page I printed was an open thread that only had 2 posts in it.

    What about the date, time, forum rules (html on , etc)?

    If a query is run for one of the members posting, is it a single query to gather signature, avatar, personal prefs (AIM, email, PM, www, etc), number of posts, member since date and location? Or is there one query for each of those?

    I want to take the passenger and back seats out, take off the muffler, take everything out of the trunk, remove the a/c and strip out the radio, so this thing will go as fast as it possibly can. (Well, maybe I'll leave the a/c in there.)

  4. #4
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First let me reiterate that I don't know.
    The best way to figure this out would be to open up vBullitin (or phpBB, perhaps) and see how it works/what queries are running.

    Yes, there probably would need to be a query for forum rules... I was thinking that would be part of the current viewing user query. There would also probably be a query for the actual implementation of these rules... the smiley library, vB code library, etc. The regex for those things could certainly be stored in the database.

    If a query is run for one of the members posting, is it a single query to gather signature, avatar, personal prefs (AIM, email, PM, www, etc), number of posts, member since date and location? Or is there one query for each of those?
    I would think it is one query for all of them, that seems much more efficient to me. How vB actually does it exactly, I don't know.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  5. #5
    Sidewalking anode's Avatar
    Join Date
    Mar 2001
    Location
    Philadelphia, US
    Posts
    2,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One thing you guys may not be taking into account is that generally, the post body is another table than the rest of the post info.

  6. #6
    SitePoint Addict
    Join Date
    Dec 2002
    Location
    Wisconsin
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're right. I didn't. Thanks.

  7. #7
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by anode
    One thing you guys may not be taking into account is that generally, the post body is another table than the rest of the post info.
    What do you mean? I was thinking the the posts would certainly be in a different table than threads but I'm not sure when you would need more than one table to provide details regarding one post. If you could you provide an example or explanation I would be quite grateful.

    Sam
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  8. #8
    Sidewalking anode's Avatar
    Join Date
    Mar 2001
    Location
    Philadelphia, US
    Posts
    2,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For example PHPBB has tables called topics, posts, and posts_text.

    Topics holds everything you'd expect a threads table to hold, posts contains what topic the post belongs to, the poster, time post was made etc. and posts_text contains some minimal information and the text of the post.

    I'm not exactly sure why they'd use the posts_text table. I use a similar approach in the CMS I'm making for a forthcoming site only due to allow for multiple pages, but no idea why a BB needs it.

  9. #9
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Okay.. Here is a little behind the scenes look at how vBulletin works...

    You have four basic tables that provide almost every thing you need....

    These include:
    Threads
    Posts
    User
    Templates.

    Each of these are objects and mostly contain items only related to that object.

    You also have some denormalization to speed up the system. For instance the Post table contains the username of the poster as well as the ID number so you don't have to do a second query to get the username. Threads are the same way.

    So when you go to get a list of threads for thread display, you will see a query that looks something like:

    select * from thread left join post on (threadid) where forumid=X

    The posts are needed for the dot feature so you can match posts to the user viewing and mark them as having participated. There are many things like this to try and keep queries down.

    For instance, there is a template cache built at the beginning of each page. The developers have to anticipate each and every template you are going to use and put them into a string. They can then be pulled using one single query. If you add a template to a page but don't add it to a page, you will add a query for each new template.

    By default vBulletin 2.2.X uses 14 queries on the the index page. These can be narrowed down to system info (number of users, number of posts, number of threads - one query each), forum information, thread information, and user information. It takes a query to get the templates, private message information, birthdays, users online as well. There are also behind the scenes queries for user permissions, access masks and so forth.

    vBulletin 3.0 uses a better caching system and has reduced this to 8 queries in total. You could probably reduce this to six or seven but I haven't looked at the code too indepth yet though.

    With the vBulletin 2.2.X backend, you can turn off some queries in the admin control panel and other features can be stripped out. However I wouldn't strip too many features as it would be a long term detriment to the communities growth.
    Wayne Luke
    ------------


  10. #10
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that's interesting stuff... especially the denormailzation.
    I had been curious the workings of vBullitin for awhile.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  11. #11
    SitePoint Addict
    Join Date
    Dec 2002
    Location
    Wisconsin
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! Very insightful. That information helps me quite a bit. I have someone doing much of the behind-the-scenes work for me, and I'll make sure she keeps this information in mind.

    Thank you very much!


    Jeff


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
  •