#1  
Old 06-27-2004, 08:42 AM
The Sandman's Avatar
The Sandman The Sandman is online now
Administrator
 
Real Name: Howard
Join Date: Jan 2004
Admin Experience: Advanced
Location: Tampa, Florida
Age: 55
Posts: 10,813
The Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond repute
Default New Posts / Get Daily Optimization for vB3!
For those of you running any version of vB3 check out this little gem posted by Freddie at vBulletin.org - it's a bit of vB 3.0.2!

New Posts / Get Daily Optimization
__________________
Webmaster
Reply With Quote
  #2  
Old 06-27-2004, 12:20 PM
accyroy's Avatar
accyroy accyroy is offline
Giant Hedgehog
 
Join Date: Jan 2004
Location: Accrington / Moscow
Age: 36
Posts: 274
accyroy is on a distinguished road
Default
hmmm. thats all well and good, but can anyone here explain how on earth adding the simple line:

AND sticky IN(0,1)

Makes such a difference... What does this line do exactly?
__________________
Land Rover Forum
Accrington
Reply With Quote
  #3  
Old 06-28-2004, 10:18 AM
dune's Avatar
dune dune is offline
<nothing/>
 
Join Date: Feb 2004
Admin Experience: Advanced
Location: South Florida
Posts: 561
dune is on a distinguished road
Default
It's look like they're ADDING an additional condition to the query. Doing so reduces the number of rows that will be returned as well as the number of rows that will have to be ordered.
__________________
Tony
Webmaster, my.IS (formerly IS300.NET) | Founder, Automotive Enthusiast Webmasters Group
Admin, AutomotivePhoto.com | Photographer, Galleries
Reply With Quote
  #4  
Old 06-28-2004, 01:34 PM
FansofRealityTV's Avatar
FansofRealityTV FansofRealityTV is offline
Tazmanian
 
Join Date: Feb 2004
Location: Michigan
Age: 43
Posts: 657
FansofRealityTV has a spectacular aura about
Default
Odd that it would make much of a difference, since "sticky" is always either a 0 or a 1, so that condition is always true.
__________________
Watching Survivor or American Idol? You can admit it...
Reply With Quote
  #5  
Old 06-28-2004, 02:30 PM
noppid noppid is offline
Tazmanian
 
Join Date: Jan 2004
Posts: 511
noppid is on a distinguished road
Default
What a bogus release. The code sucks and brings servers to their knees and they call it a hack? This is a bug fix. It should have been announced at .com not .org.
Reply With Quote
  #6  
Old 06-28-2004, 03:50 PM
Kall's Avatar
Kall Kall is offline
Tazmanian
 
Real Name: Ace
Join Date: Apr 2004
Admin Experience: Advanced
Location: New Zealand
Age: 35
Posts: 1,498
Kall is just really niceKall is just really nice
Default
After reading up on it yesterday, all I could find was that by doing things this way, a 'quirk' of mysql is utilised.

Or something.

Something to do with null results and other stuff over my head.

@noppid: Searching on my forums has never brought it to its knees...nor have I noticed anyhting similar happening at any of the vb-powered sites I visit....or have I misunderstood your comment?
__________________
I provide vBulletin Hosting and other vBulletin Services - Let me know if you need anything.
Reply With Quote
  #7  
Old 06-28-2004, 04:07 PM
noppid noppid is offline
Tazmanian
 
Join Date: Jan 2004
Posts: 511
noppid is on a distinguished road
Default
Quote:
Originally Posted by Kall
After reading up on it yesterday, all I could find was that by doing things this way, a 'quirk' of mysql is utilised.

Or something.

Something to do with null results and other stuff over my head.

@noppid: Searching on my forums has never brought it to its knees...nor have I noticed anyhting similar happening at any of the vb-powered sites I visit....or have I misunderstood your comment?
In the post by the guy that released it he states...

Quote:
For example on vb.com, we went from doing a scan of 107000 thread rows on every getnew/getdaily (that wasn't cached in search.php) to a scan of 188 rows.
Perhaps my useage of the word was too strong, but a 99.8% improvement in efficentcy is significant. I find it appalling that the author or represetative of the author would release such an improvment as a hack. The part where he says to another member over there that jelsoft policy wasn't up for discussion got to me. It looks like a cover up. But that's the org for ya.
Reply With Quote
  #8  
Old 06-28-2004, 04:32 PM
dune's Avatar
dune dune is offline
<nothing/>
 
Join Date: Feb 2004
Admin Experience: Advanced
Location: South Florida
Posts: 561
dune is on a distinguished road
Default
If the sticky column can be NULL, then putting in that extra WHERE clause will not select those rows that are NULL. Apparently, there are many more rows where sticky is NULL than it is not...
__________________
Tony
Webmaster, my.IS (formerly IS300.NET) | Founder, Automotive Enthusiast Webmasters Group
Admin, AutomotivePhoto.com | Photographer, Galleries
Reply With Quote
  #9  
Old 06-28-2004, 05:46 PM
noppid noppid is offline
Tazmanian
 
Join Date: Jan 2004
Posts: 511
noppid is on a distinguished road
Default
Quote:
Originally Posted by dune
If the sticky column can be NULL, then putting in that extra WHERE clause will not select those rows that are NULL. Apparently, there are many more rows where sticky is NULL than it is not...
That was exactly how I took this, but that seems to prevent relevent threads from being shown because of sticky being NULL.

If sticky can be NULL, then why? Is that a bug? I don't have any threads with sticky set NULL, but I'm curious now what brought this "patch" out.
Reply With Quote
  #10  
Old 06-28-2004, 06:49 PM
asiansINC's Avatar
asiansINC asiansINC is offline
asiansINC.com
 
Join Date: May 2004
Location: New York, NY
Age: 34
Posts: 49
asiansINC is on a distinguished road
Default
Apparently it's going to be part of the 3.0.2 code when it's released.
__________________
Richard Young, Esq. - Asian Forums
Make $10k every week on autopilot with the Reverse Funnel System
Reverse Funnel System Review | Reverse Funnel System Review
Reply With Quote
  #11  
Old 06-28-2004, 07:05 PM
FansofRealityTV's Avatar
FansofRealityTV FansofRealityTV is offline
Tazmanian
 
Join Date: Feb 2004
Location: Michigan
Age: 43
Posts: 657
FansofRealityTV has a spectacular aura about
Default
"sticky" can't be null, from what I saw. I did a random scan through about 700 of my threads in my database, and "sticky" is either a 0 (unstuck) or 1 (stuck).

So, it must just be a fluke with mySQL, because it normally wouldn't optimize the search function at all.
__________________
Watching Survivor or American Idol? You can admit it...
Reply With Quote
  #12  
Old 07-08-2004, 06:21 PM
empress empress is offline
TAZ Rookie
 
Join Date: Jul 2004
Posts: 2
empress is on a distinguished road
Default
Quote:
Originally Posted by asiansINC
Apparently it's going to be part of the 3.0.2 code when it's released.
was this added in the 3.0.3 release?
Reply With Quote
  #13  
Old 07-08-2004, 07:36 PM
The Sandman's Avatar
The Sandman The Sandman is online now
Administrator
 
Real Name: Howard
Join Date: Jan 2004
Admin Experience: Advanced
Location: Tampa, Florida
Age: 55
Posts: 10,813
The Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond reputeThe Sandman has a reputation beyond repute
Default
Quote:
Was this added in the 3.0.3 release?
__________________
Webmaster
Reply With Quote
  #14  
Old 07-09-2004, 11:03 AM
dune's Avatar
dune dune is offline
<nothing/>
 
Join Date: Feb 2004
Admin Experience: Advanced
Location: South Florida
Posts: 561
dune is on a distinguished road
Default
Quote:
Originally Posted by FansofRealityTV
"sticky" can't be null, from what I saw. I did a random scan through about 700 of my threads in my database, and "sticky" is either a 0 (unstuck) or 1 (stuck).

So, it must just be a fluke with mySQL, because it normally wouldn't optimize the search function at all.
Try this query:

select sticky, count(*) AS numtimes
from [tablename]
group by sticky

You should get a result like:

sticky numtimes
1 xxx
0 xxx

and possibly:
{null} xxx
__________________
Tony
Webmaster, my.IS (formerly IS300.NET) | Founder, Automotive Enthusiast Webmasters Group
Admin, AutomotivePhoto.com | Photographer, Galleries
Reply With Quote
  #15  
Old 07-09-2004, 12:56 PM
Wayne Luke's Avatar
Wayne Luke Wayne Luke is offline
Tazmanian
 
Join Date: Jan 2004
Admin Experience: Guru
Location: Southern California
Posts: 2,897
Wayne Luke is a name known to allWayne Luke is a name known to allWayne Luke is a name known to allWayne Luke is a name known to allWayne Luke is a name known to all
Default
It forces the use of an index whereas it wasn't using the index previously and doing a manual sort every time the query was run.

Basically it gets around a quirk in MySQL where the appropriate indices are not always used for whatever reason. MySQL wouldn't even use the query if force index directives were used.
Reply With Quote
  #16  
Old 07-09-2004, 01:55 PM
dune's Avatar
dune dune is offline
<nothing/>
 
Join Date: Feb 2004
Admin Experience: Advanced
Location: South Florida
Posts: 561
dune is on a distinguished road
Default
Quote:
Originally Posted by Wayne Luke
It forces the use of an index whereas it wasn't using the index previously and doing a manual sort every time the query was run.

Basically it gets around a quirk in MySQL where the appropriate indices are not always used for whatever reason. MySQL wouldn't even use the query if force index directives were used.
Aha! Interesting.
__________________
Tony
Webmaster, my.IS (formerly IS300.NET) | Founder, Automotive Enthusiast Webmasters Group
Admin, AutomotivePhoto.com | Photographer, Galleries
Reply With Quote
Reply

  Admin Zone Forums > The Community Zone > Managing an Online Community > Community Organization





Currently Active Users Viewing this Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Policies, TOS, Notices (etc) Content Sharing Thread FlyBoy73 Members & Staff 15 01-06-2005 11:07 AM
Ex-mod steals posts & entire forums Anonymous Members & Staff 6 04-27-2004 08:01 PM
vb3 gold...show posts from last x days jilly Community Organization 3 04-06-2004 09:18 PM


 

All times are GMT -4. The time now is 04:41 PM.


Powered by: vBulletin
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Page generated in 0.09502196 seconds with 13 queries
The Admin Zone copyright 2003-2014 All Rights Reserved. Content published on The Admin Zone requires permission for reprint.