SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Hybrid View

  1. #1
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi everyone once again,
    Quick query regarding queries...

    I'm creating a Yahoo! style directory (only a couple of hundered links) as a section of my site. I intend to put this month's and last month's "Site of the Month", two adverts (including my main advertising banner), the top ten links from the directory (based on clicks) and obviously the directory itself.

    All of these things are pulled by a MySQL db. I just ran through the script and found that it has around 15 db calls in total. Is this far too many or does it really matter a great deal?

    I'm not expecting hundereds of thousands of users to be using the page at the same time but I would like it to be able to handle around 200 simultaneous hits (or somewhere in that region).

    Exactly how many is too many?

    Thanks very much for your help in advance,

  2. #2
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    When I was creating a site for me i was told from somebody (Freddydoesphp) that you should keep to a minimum.

    He also recommended to me that around 2 queries is a max you should use really unless you want to create slower loading pages and a higher server load so I stuck to this.

    I think you could limit this down to 3 queries at least.

    Peter

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The other thing to ask yourself is how dynamic are the this month and last month's "site of the month"? They sound fairly static to me (if they only change each month). Same with the banner links - how often do these change. If you can manually update these through an include file then that's going to save a couple of database hits.

  4. #4
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK,
    I was thinking of putting the sites of the month and advert into an include but the ad is served from my ad software so that I can monitor the stats, this could be put into an include, I know, but I was led to believe that includes were more server processor heavy than DB calls. Is this the case?

    For the directory, I obviously have categories and sub categories. Currently, the 4 main categories on display are queried from the DB (one query) and put into an array, then, the 3 subcategories of each main category are queried (1 x 4 main categories = 4 queries) and output to the screen.

    Is there anyway that I could pull the 4 main categories and 3 subcategories (for each main one) into an array and output it?

    Currently, my categories table has a "CatID" which is the unique category ID for each category and a "ParentCat" column which indicates which subcategory belongs where.

    ie:-

    Main Category A (CatID = 1, ParentCat = 0)
    SubCategory of A (CatID = 2, ParentCat = 1)
    SubCategory of A (CatID = 3, ParentCat = 1)
    SubCategory of SubCategory of A (CatID = 4, ParentCat = 3)

    ...so on and soforth.

    Any tips?

  5. #5
    SitePoint Zealot
    Join Date
    May 2000
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    The directory I developed simply updates all link pages once every week. Its pointless querying the database for links every time a user accesses a category page, since the link info is not really going to change that often.

    The static HTML also is a plus point when it comes to search engines.

    My table of categories is similar to yours:

    Parent ID Category ID (key) Sort_Ident
    0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1
    1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2
    0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3


    Basically when I retrieve the categories, I go:
    SELECT parent, category FROM category ORDER BY sort_ident ASC

    That should retrieve everything, and in the correct order too.

    When I add a new category, the "sort_ident" column is re-calculated to reflect the changes.

    Example:

    Parent ID Category ID (key) Sort_Ident
    0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1
    1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2

    1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3
    ^ new entry

    0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4


    Hope that helps.

  6. #6
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it is defenitely a good plan to reduce the number of sql calls but so you don't worry, vbulletin uses about 15 sql calls on forumdisplay

  7. #7
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes but vBulletin is also pulling a lot more info than just a URL and a site title...

    Not to minimize your project or anything, just putting it in perspective.
    Kevin

  8. #8
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Depends on your machine, but I would recommend not worrying a lot about the number of queries if you are minimizing them the most you can. 5 queries should be a good number to pull a lot of info in a front page.

    If you check Vbulletin it makes a lot of queries per page, and the loading time isn't bad.

  9. #9
    SitePoint Enthusiast nguip's Avatar
    Join Date
    Apr 2001
    Location
    Malaysia
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm...

    This is interesting. So, basically we would be worrying about how many sql query in one page is it ?

    I see vbulletin (sitepoint) sometime show the msg:-

    The server is busy now. Please try again later.

    Is this the problem caused by too many sql query in one page ?
    Ngu I.P.
    Web Developer

  10. #10
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One of the features of vBulletin is that it allows the admin to set a limit at which vBulletin will start turning away visitors, to keep a server from overloading. This also may be the server itself turning away visitors.

    But in any case, no it is not caused by too many SQL queries on one page.
    Kevin

  11. #11
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by tubedogg
    One of the features of vBulletin is that it allows the admin to set a limit at which vBulletin will start turning away visitors, to keep a server from overloading. This also may be the server itself turning away visitors.

    But in any case, no it is not caused by too many SQL queries on one page.
    well.... it is when the server load reaches a certain amount, extra SQL queries create extra server load so it is part of the process. As sitepointforums are on a dedicated server (i think) the only thing causing server loads really are going to be httpd process, mysql processes and perhaps a bit of email sending and ad scripts running so yes extra mysql calls are going to be a large constituant of why people get turned away.

  12. #12
    One website at a time mmj's Avatar
    Join Date
    Feb 2001
    Location
    Melbourne Australia
    Posts
    6,282
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I was told that vBulletin can handle a lot more simultaneous users than it does at sitepointforums, I'm not sure but way over 4 times the traffic, and SPF admins have imposed a conservative limit, to be on the safe side.
    [mmj] My magic jigsaw
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The Bit Depth Blog Twitter Contact me
    Neon Javascript Framework Jokes Android stuff

  13. #13
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It can. It can handle 400 or 500 users pretty comfortably, depending on the server and how well it's optimized, and whether you're using a dedicated DB server and a separate server for the web server.
    Kevin

  14. #14
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by padders
    so yes extra mysql calls are going to be a large constituant of why people get turned away.
    Yes that is true. But the question was "Is this the problem caused by too many sql query in one page?" and I would say the answer to that is no. As long as the number of calls doesn't get too exorbitant, you're going to have to be turning people away regardless if there are 10 calls on a page or 20, if the server load gets too high.
    Kevin

  15. #15
    SitePoint Wizard
    Join Date
    Apr 2000
    Posts
    1,483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by mmj
    I was told that vBulletin can handle a lot more simultaneous users than it does at sitepointforums, I'm not sure but way over 4 times the traffic, and SPF admins have imposed a conservative limit, to be on the safe side.
    vB can handle it but this particular server can't - if vB was placed on the right server then yes 400 concurrent users shouldn't give it a major problem.


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
  •