[MTOS-dev] Feedback requested: changing 'junk_status' on comments and pings

[Hirotaka Ogawa]

On Sat, Apr 12, 2008 at 2:30 AM, Brad Choate <brad at sixapart.com> wrote:
> In doing our performance analysis of the MT schema, something came up
>  that was surprising to me: comment listing screens are pretty slow for
>  large data sets. As it turns out, the problem lay with our use of the
>  junk_status column in our comment and ping tables.
>
>  The junk_status column is typically either set to -1 (meaning, this
>  record is junk) or 1 (non-junk). But we will also store a value of 0
>  for this column for cases where no scoring mechanism actually flagged
>  the item as junk or not-- so an indeterminate state.
>
>  This ternary state column forces us to do selections "where
>  junk_status in (0,1)" in order to capture all non-junk records
>  irrespective to publishing state. This is the root of the performance
>  problem: mysql won't optimize this and it winds up doing a filesort in
>  the end.

Brad, I can't believe this so soon.

At least, in my environment (MySQL 5.0.45), both seem to be using filesort:

EXPLAIN SELECT * FROM mt_tbping WHERE (tbping_junk_status IN
('0','1')) AND (tbping_blog_id = '1') ORDER BY tbping_created_on DESC
LIMIT 26;
EXPLAIN SELECT * FROM mt_tbping WHERE (tbping_junk_status = '1') AND
(tbping_blog_id = '1') ORDER BY tbping_created_on DESC LIMIT 26;


>  I've tried changing the query to say "where junk_status >= 0", or
>  "where junk_status != -1" or "where junk_status =0 or junk_status =
>  1". Nothing helps it. So, that leaves me with one other option: change
>  this column to a binary state.
>
>  For the sake of backward compatibility, I think the states should be
>  -1 (junk) and 1 (non-junk). Switching to 0 for non-junk and 1 for junk
>  would likely break other plugins or handwritten SQL that may exist out
>  there that selects for -1 and 1 specifically. I've made all the app
>  changes to set and select by these and it has indeed made a marked
>  improvement.
>
>  The only other question is how best to upgrade the schema. My thinking
>  so far:
>
>         junk_status = -1, leave as-is
>         junk_status = 1, leave as-is
>         junk_status = 0, assign 1
>
>  Basically, records that have a junk_status of 0 are ones that are
>  either moderated or did not achieve a junk score that would classify
>  them as junk, so by definition, they are not junk. I _was_ thinking
>  about a complex matrix of checking the 'visible' flag and for any
>  existing non-null junk_score and attempting to assign junk_status on
>  that, but those are typically cases where an item was scored as junk
>  and the user published it, then later moderated it. Or perhaps a
>  situation where a comment was scored as non-junk but then moderated
>  (moderating an item sets the junk_status to 0, but won't going forward).
>
>  How does this course of action sound? Can you see other alternatives
>  to what I'm suggesting? Can you forsee any problems?
>
>  Thanks,
>  -Brad
>
>  --
>  Brad Choate
>  Engineering Manager, Movable Type, Six Apart, Ltd.
>  http://www.sixapart.com/movabletype/
>  Mobile: (918) 271-0105  -  AIM: bschoate
>
>
>
>  _______________________________________________
>  MTOS-dev mailing list
>  MTOS-dev at sixapart.com
>  http://www.sixapart.com/mailman/listinfo/mtos-dev
>



-- 
Hirotaka Ogawa
http://twitter.com/ogawa
http://as-is.net/blog/