SitePoint Sponsor |
|
User Tag List
Results 1 to 15 of 15
-
Mar 30, 2001, 08:32 #1
- 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,
-
Mar 30, 2001, 09:43 #2
- 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
-
Mar 30, 2001, 09:56 #3
- 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.
-
Mar 30, 2001, 10:40 #4
- 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?
-
Mar 30, 2001, 19:20 #5
- 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 1 1
1 2 2
0 3 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 1 1
1 2 2
1 4 3
^ new entry
0 3 4
Hope that helps.
-
Apr 17, 2001, 13:09 #6
- 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
-
Apr 17, 2001, 18:43 #7
- 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
-
Apr 17, 2001, 19:09 #8
- 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.
-
Apr 17, 2001, 19:37 #9
- 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
-
Apr 17, 2001, 19:44 #10
- 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
-
Apr 17, 2001, 19:53 #11
- 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.
-
Apr 18, 2001, 04:52 #12
- 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
-
Apr 20, 2001, 21:40 #13
- 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
-
Apr 20, 2001, 21:47 #14
- 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.Kevin
-
Apr 20, 2001, 22:47 #15
- 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.
Bookmarks