New Posts / Get Daily Optimization for vB3!

Discussion in 'Community Organization' started by The Sandman, Jun 27, 2004.

  1. The Sandman

    The Sandman Administrator

    24,503
    1,822
    +2,399
    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
     
  2. accyroy

    accyroy Giant Hedgehog

    548
    300
    +2
    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? :)
     
  3. dune

    dune Habitué

    1,122
    0
    +1
    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.
     
  4. FansofRealityTV

    FansofRealityTV Habitué

    1,314
    695
    +6
    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.
     
  5. Anonymous

    Anonymous Habitué

    1,152
    613
    +287
    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.
     
  6. Kall

    Kall Devotee

    2,996
    685
    +11
    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?
     
  7. Anonymous

    Anonymous Habitué

    1,152
    613
    +287
    In the post by the guy that released it he states...

    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.
     
  8. dune

    dune Habitué

    1,122
    0
    +1
    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...
     
  9. Anonymous

    Anonymous Habitué

    1,152
    613
    +287
    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.
     
  10. asiansINC

    asiansINC asiansINC.com

    98
    11
    +0
    Apparently it's going to be part of the 3.0.2 code when it's released.
     
  11. FansofRealityTV

    FansofRealityTV Habitué

    1,314
    695
    +6
    "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.
     
  12. empress

    empress Neophyte

    4
    51
    +0
    was this added in the 3.0.3 release?
     
  13. The Sandman

    The Sandman Administrator

    24,503
    1,822
    +2,399
    :yup:
     
  14. dune

    dune Habitué

    1,122
    0
    +1
    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
     
  15. Wayne Luke

    Wayne Luke Tazmanian

    5,793
    0
    +35
    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.
     
  16. dune

    dune Habitué

    1,122
    0
    +1
    Aha! Interesting.
     
Verification:
Draft saved Draft deleted