300 queries in forum permissions?!

[Billy]

I don't know if there is any truth to this, but I was told somewhere that the forum permissions area in the admin cp took 300 queries with 8 forums and when adding more forums, the query count increases.

IS this true? Because I know that 300 queries to produce a page is NOT good.

[The Prohacker]

To see how many queries, add &explain=1 to the end of the url.

With the default one forum and one catagory I see 11 total..... And this is 2.0.3, so it could have improved by now, so I highly doubt 300 unless they have a crap load of forums....

[tubedogg]

The number of queries used to generate that page is as follows:
1 base query.
2*the number of forums you have.
1*the number of usergroups you have.

So say you have 8 usergroups (about standard) and 10 forums. 2*20=20 + 1*8=8 + 1 = 29. Nowhere near 300.

Admin pages are optimized heavily just as everything else is but sometimes there is no way around a lot of queries (update counters, for example). It is used because the Admin CP is not subject to the foot traffic that your forums are.

[eva2000]

Originally posted by tubedogg
The number of queries used to generate that page is as follows:
1 base query.
2*the number of forums you have.
1*the number of usergroups you have.

So say you have 8 usergroups (about standard) and 10 forums. 2*20=20 + 1*8=8 + 1 = 29. Nowhere near 300.

Admin pages are optimized heavily just as everything else is but sometimes there is no way around a lot of queries (update counters, for example). It is used because the Admin CP is not subject to the foot traffic that your forums are. wow

let's see for me that would be

1 + (2 x 140 forums) + (1 x 14) = 295 queries

so billy i doubt having only 8 forums would amount to 300 queries unless you had 100s of usergroups

[Chen]

Originally posted by eva2000
wow

let's see for me that would be

1 + (2 x 140 forums) + (1 x 14) = 295 queries

so billy i doubt having only 8 forums would amount to 300 queries unless you had 100s of usergroups
:eek:

[Kaizen]

:eek:

How does your server take it!?

[eva2000]

takes it easily :)

171 queries/second on searchindex reindexing - check it out http://vbulletin.com/forum/showthread.php?s=&threadid=32147&highlight=queries

remember that page isn't regularly accessed only when adding new forums or loading that page..

[Kaizen]

lol

you have to re--index overnight

hehehehhe

never taken me more than 20 minutes.

[eva2000]

yeah that's because i had 452,000+ posts 29,000 threads and wanted to reindex

with min 3 and max 17 characters

now i have 9+ million records in searchindex :)

also vB ain't the only thing on my server.. i have several live sites which get as much traffic or more than my vB forum :D

[Kaizen]

*me waits for eva's server to crash

*me thinks, not long now !!!

[Kaizen]

oh and btw

isn't your sig getting a little big

I got a pm the other day from some user asking me to shorten.

I agreed and did !

But yours just gets bigger everyday - lol

[eva2000]

LOL... my sig's a permanent fixture around here take a read http://vbulletin.com/forum/showthread.php?s=&threadid=23421 :D

[Kaizen]

hehehe

:D :D :D

btw you took the link out to calculate hits on pages

mysql hit counter or something

whats the link for that ?

Also:

I heard you were talking about english colleges/uni etc.

R u coming here for your education ?

[Billy]

Are you guys looking at the right place, he said there were 10 groups and 8 forums. He said in forumpermissions that it was 300 queries.

[Billy]

Oh, I don't know if this matters, but he said that some of your queries aren't counted in the count, so he added those to the count and that is where he got the 300 from.

[eva2000]

not i just checked by adding ?showqueries=1 to the end of the forumpermission php file and it looks like

5 queries to each forum + 2 queries per usergroup per forum??????????

i know i'm reading that wrong :o

[Freddie Bingham]

We allow everyone in the world to see the exact queries we are running and how many are being executed. You simply have to add &showqueries=1 to any url on any vbulletin board. You should only do it on this forum though since inefficient hacks may be installed by end-users.

When we design the database we set up the structure to provide maximum benefit to the queries that are spawned by the forum pages. These are the pages that see great usage based on the amount of users you have.

Doing this does not always leave us with the "perfect" query that needs to be executed in the Admin CP. We could add Index A and Index B or Lookup field Y to any given table and that would speed up Admin functions and lessen queries but it would adversely slowdown forum usage. We don't consider the Admin CP to be a high resource area as we expect it to only be used a handful of times on average per day. We optimize the admin queries but we need a lot of information to perform some functions in the Admin CP so at times, yes, there may seem to be an excessive amount of queries to determine permissions or other related items. I couldn't tell you how many queries any page in the admin CP uses but that number would be based off of how many forums and usergroups you have. We have a balance beam and we stack everything on that beam over to the side of the forums. Others may choose to stack that beam more towards the middle.

[tubedogg]

Originally posted by Billy
Oh, I don't know if this matters, but he said that some of your queries aren't counted in the count, so he added those to the count and that is where he got the 300 from. What would be the point of that (us hiding queries I mean) when anyone with a license could pop open forumpermissions.php in a text editor and count the queries (as I did)?

[LDT]

Holy cr*p! 1121 queries on my forums permission page!
26 queries per forum... Not complaining here, just saying that it's very likely that your friend indeed has 300 queries on that page. :)

[Wayne Luke]

How many times a day do you access the admin/forumpermissions.php?

1? 2? Certainly not 150,000 times which is what I get at SitePointForums.com for the index page alone.

Sometimes you have to draw a line between optimization and functionality. Now the forumpermissions control panel gives you a lot of minute detail about who can access what forum and to what degree. This power comes at a price. However unless you are sitting there and refreshing it all day, the number of queries shouldn't be a problem in the load of your server. If these queries were on the main index (index.php) that all your members access regularly, then there would be cause for alarm.

Having intensive administration routines like this is normal for database applications. I once wrote an indexing routine that had to process 20,000 queries a second in order to get the job done in less than 96 hours. Not for vBulletin of course but for a much larger system with over 800 tables and a quarter of a billion records. However since it was only used once every 6 months, the resources needed to get the job done were not an obstacle.

[Hooper]

Originally posted by wluke

Having intensive administration routines like this is normal for database applications. I once wrote an indexing routine that had to process 20,000 queries a second in order to get the job done in less than 96 hours. Not for vBulletin of course but for a much larger system with over 800 tables and a quarter of a billion records. However since it was only used once every 6 months, the resources needed to get the job done were not an obstacle.


Good Golly Miss Molly.