Materialized Subquery Optimization

Greatest N per group is a hard problem. Everytime I comes up in my project at work (essentially a custom customer relation management system built in CodeIgniter) I end up wrestling with trying to do things the way that feels right but MySQL ain't havin' it. I usually end up having better luck saying to Hell with it and just rolling a Select N + 1 antipattern. Shipped beats perfect. I can beat myself up about sucking at database design later. At the moment I have problems to solve.

But today I discovered something interesting. I need to export the most recent values per account out of a couple different tables. Classic greatest N per group problem. I write a fairly ugly set of nested queries with the necessary ORDER BY and GROUP BY tomfoolery to get the behavior I want out of MySQL. I run it here on my local dev instance. It executes in a little under 4 minutes. Far from great, but for what should be a one-off across 200,000 accounts it's something I can live with. I switch over to production. Wait way longer than 4 seconds. As I approach a full minute I halt the query and scratch my head. I throw an EXPLAIN in front of the query in both environments.

The first thing I notice is while my local environment produces the query execution plan almost instantly (under 1ms) over in production the explain statement takes over 10 full seconds to run. Then I notice the output is also quite different. Locally I see the select_type MATERIALIZED. I'm familiar with materialized views even though MySQL doesn't support them. (At least not in the same way systems like PostGRES or Oracle do out of the box.) And I figured this difference in the output of the associated execution plans was an important clue.

A few weeks ago my early 2011 Macbook Pro gave up the ghost. So I'm running on new hardware. MySQL doesn't ship as part of MacOS so when I set up my stuff I (perhaps naively) just rolled with the most recent version. Let's just say our production server isn't quite so up to date.

The difference in query optimization between 5.5 and 5.6 is significant. Maybe I don't suck as database design so much afterall. Maybe I've just been wrestling with the query optimizer. (I probably still suck, just — ya know — less.)

The choice quote that is most relevant to the issues I was wrestling with today:

With Subquery Materialization, a subquery is executed once and the result is stored. In MySQL 5.5, the subquery would instead be executed for each row in the outer query. Our tests show that with a scale factor of 10, DBT-3 query 18 execution time drops from months to 68 seconds.

It's possible that my painfully slow but livable ~4 minute query in 5.6 would take months or even a year or more to run under 5.5. That's insane.

Adventures in Geek Parenting: Chore Wars

The Setup

We're trying to replace assigned chores and allowances with Chore Wars. Keep in mind that allowances are already earned on a per-chore basis under the old system. You only got paid for what you did and could document. (Just like real life!) The old system had a number of assigned chores equal to each child's age and they earned $1 per chore completed for the week. We were open to negotiating extra pay for extra work were either child really needing to generate some income. But I don't know that the option was ever exercised. Maybe once by the teenager?

For Chore Wars I'm moving away from assigned chores 'cos that doesn't fit the gamification aspects. Once it's assigned it becomes a true chore, not a game-like challenge. It's possible the kids will even start to compete with each other to maximize their own earning potential. It's also possible to cooperate, but it's up to them to work out who gets credit for what within the Chore Wars system.

Also, rather than an all or nothing for the week approach, you can claim the vast majority of chores (or Adventures as Chore Wars calls them) once per day. This also means that rather than being worth $1 if completed for the week each chore is worth $0.10-$0.25 with a few particularly challenging Adventures still worth $1 per (cleaning a bathroom for example). There's also a random element in the pay out, most adventures have a range for the "gold" you earn upon completion, which the system determines randomly.

My idea here is to encourage daily tidying rather than a weekly mad-dash. One side effect being if you sweep the office today and you also swept it yesterday it should be much easier and you can earn your chore pay out that much more quickly. But we have to hit a certain critical mass of actually doing stuff before the lesson clicks.

Every time I sit the kids down and explain how things are supposed to work, we see an explosion of activity. But this quickly falters after a day or two. They say it's because I hardly ever pay out. But I say I see no point in paying out when they've barely earned $1 for the week. This weekend I made my pitch again. And the teenager has really dove into the task. The 9 year old is still kinda draggin' ass. But maybe he'll get there eventually.

Current Experiment

I say all that just as back-story for my actual purpose in writing this. In addition to the in-game gold system I'm basing pay outs on, there's an XP system and a treasure system. I'm brainstorming on ways to leverage those for added incentive to actually do chores and log them daily.

Another goal here is to build family memories. So I'm trying to organize/structure evening family activities around that goal. Then offer incentives based on the Chore Wars system. Currently my thinking goes something like this.

Organized Family Activity Time

I'd like to set aside 7 to 9pm each night for family activity time. There are some assumptions already built into this. On work days I get home around 5:30. If we can get dinner served by 6:15 or so then we can have the dishes put away by 7 and dive into family activity time. This also assumes the house is at least tidy enough to be livable if not perfect and the kids have all their school work done. If this isn't the case we all pitch in to get there. Family activity time ends at 9 so if we don't have our collective act together then we all pay a price.

Each week night has an assigned activity. I haven't gone so far as to assign these to a specific night yet, but the 5 structured activities I can come up with are:

1. Board Game Night
2. Reading Aloud Night
3. Netflix / TV Night
4. Music / Jam Session Night
5. Video Game Night

The weekly leader in terms of XP in Chore Wars gets to pick the specific thing we do within that night's activity. Saturday and Sunday nights are freestyle, and the XP leader gets to pick both the type of activity as well as the specific thing we do that night. There's also random treasure items that we collect. I'm considering allowing those to be bid to hijack the normal XP leader process. Or maybe make them worth a temporary XP bonus for the purpose of calculating the winner for the day.

Risks

We've tried stuff like this in the past and it has never stuck. Essentially it only takes one of us to go all Negative Nancy on the idea to derail it for the rest of us. Also making a schedule and sticking to it is hard, you guys! The gamification elements are new and hopefully will help motivate us to actually stick with it. I have not budgeted in time for things like an exercise routine. So getting that in there piles the assumptions even higher than they currently are. Once we try to roll this out to production we may have to reduce family time to 90 or 60 minutes. But that will limit the choices available for some of the activities. Maybe that's part of the weekend freestyle. We can play longer games or watch longer movies or whatever. That gets a little easier to pull off 'cos the school work should still be done from Friday night. We'll see.

Future Development

If the incentives for experience points catch on there's added arbitrary challenges we can introduce. The Chore Wars system already allows you to claim partial or bonus XP for any given Adventure, the mechanics of which are left up to the users. So, for example, completing a task while whistling may be worth 25% bonus XP while walking backwards throughout the task may be worth a 50% bonus. Do both and claim double XP. Introducing such things at this point would just be adding complication to a system we're still not yet fully using. But it's good to have ideas to grow on as needed.

Chore Monster

For anyone thinking of trying something similar, there is an alternative to Chore Wars that seems a bit more modern and app based: Chore Monster.

Necessary Evil's power system as an adaptive framework for super heroic gaming in Savage Worlds

One of my favorite aspects of the core Savage Worlds rules is the adaptability. There's no "exotic weapon" training, just pick your weapon (including bare hands) and make a Fighting roll. If you wanna model being particularly good with a certain weapon or fighting style there are Edges to do that.

This carries over to the magic / power system. There's no Magic Missile or Acid Arrow. You get Bolt. That can model any magical power that deals damage to a single target at range. The rest is just window dressing. And that's handled through Trappings. One character can use a fire trapping while another uses an acid trapping. The end result is still some mofo is takin' 2D6 damage from across the room. Accept no substitutes.

If you want your Trappings to have an actual mechanical effect there are ways to add that to your games. That sort of thing is usually left up to the specific setting.

Necessary Evil introduces a whole new power system to handle the sort of street level comic book action not really possible under the core rules alone. Thankfully they carry over the adaptive powers + player chosen trappings. Not only does this allow players to model a wide variety of super power concepts from a fairly concise set of rules, it often also supplies multiple possible ways of getting your intended power, depending on how picky you are about how the specific game mechanics play out.

For example, Gru's freeze ray from Despicable Me. That is most certainly a Device, which qualifies it for a 1 Power Point discount for ever 5 Power Points invested into it. (This is a fairly universal Modifier in the Necessary Evil powers system.) But how exactly should we invest those Power Points?

We could just call it Stun with a cold trapping. As a Device that costs us a single Power Point! The Shaken status isn't a perfect fit for what we see in the movies but you get what you pay for.

Another option is a level of Attack: Ranged + Elemental Trick: Cold for a final cost of 4 Power Points after the discount for Device. This version does damage and "slows" the target by lowering their initiative order, still not exactly what we see in the movie. But it fits the general idea of a "freeze ray" very well.

Or we could go with Ensnare + Ranged Attack modifier. This gets us much closer to exactly what Gru's gun does for the same 4 Power Point cost as the previous option.

Finally, we could combine Paralysis and a level of Attack: Ranged via Paralysis's Extra Range modifier for a total cost of 7 Power Points after the discount for Devices. This is the most expensive option, but also gets us closest to what we see in the movies: targets physical covered in ice and completely unable to move for at least a few seconds.

Personally I wouldn't sweat the exact mechanics of it all and just go with the 1 point Stun with cold trapping option. I mean, Gru's gonna need every extra Power Point he can muster to pay for all those Minions. ;)

Pre-Generated Characters

I kinda cheated and went with the "Race as Character Class" methodology. Not that Savage Worlds has Character classes per se. I just mean I came up with a single concept per non-Human Race. So all my Wood Elves are archers, all my Sun Elves are mages, etc. For Humans I came up with 4 for a total of 10 basic character concepts. For each of those I have a common block of stats and a short list of Edges and Hindrances that would work well with the concept.

I located art to serve as character portraits for 2 male and 2 female versions of each concept. I made choices on Edges, Hindrances, and Gear purchases inspired by the character art. At the end of this process I had 40 pre-generated characters for my 8 players to choose from. For the most part they never noticed that they are really only dealing with slight variations on 10 themes.

I named each character using the WotC Character Name Generator and wrote up a brief blurb on the personalities and combat strategies for everyone (which my players ignore for the most part, but at least they've got the option of taking some guidance in role playing and combat situations).

Basic Character Creation Guidelines

• Attributes start at d4
• 5 Attribute points
• 15 Skill points
• Take up to 4 points of Hindrances
  
  • 2 Hindrance points buys an Edge or Attribute point
  • 2 Hindrance points buys a Skill point or doubles starting funds
• $1000 starting funds

The Character Concepts

Moon Elf Assassin

Stats

Agility D8

   Fighting D8

   Lockpicking 0

   Shooting D8

   Stealth D8

Smarts D6

   Healing 0

   Notice D6

   Taunt D6

Spirit D6

   Intimidation D6

   Persuasion 0

Strength D6

Vigor D6

---

Charisma -2

Pace 10

Parry 6

Toughness 5

Potential Edges

Acrobat, Assassin, Fleet Footed, Florentine, Rich, Filthy Rich, Strong Willed

Potential Hindrances

Anemic, Arrogant, Big Mouth, Mean, Overconfident, Stubborn, Ugly, Vengeful, Wanted

Gear

$1000

Powers

Wall Walker (racial ability)

Sun Elf Mage

Stats

Agility D6

   Fighting D8

   Lockpicking 0

   Shooting D4

   Stealth D4

Smarts D6

   Healing 0

   Notice D4

   Taunt 0

Spirit D8

   Intimidation D6

   Persuasion D6 Magic D10

Strength D4

Vigor D6

---

Charisma 0

Pace 6

Parry 6

Toughness 4

Potential Edges

Adept, Arcane Background, Arcane Resistance, Brawny, Common Bond, Nobel

Potential Hindrances

Anemic, Cautious, Clueless, Curious, Pacifist, Vow

Gear

$1000

Powers

Detect/Conceal Arcana (free action), Elemental Manipulation (free action) Flight (racial ability)

Pick 3 from: Armor, Blind, Bolt, Boost/Lower Trait, Burrow, Confusion, Deflection, Entangle, Environmental Protection, Fear, Healing, Light/Obscure, Smite, Speed, Stun, Succor, Summon Ally

Power Points 20

Wood Elf Marksman

Stats

Agility D8

   Fighting D8

   Lockpicking 0

   Shooting D8

   Stealth D8

Smarts D8

   Healing D8

   Notice D8

   Taunt 0

Spirit D4

   Intimidation 0

   Persuasion 0

Strength D4

Vigor D6

---

Charisma 0

Pace 6

Parry 6

Toughness 5

Potential Edges

Level Headed (racial edge), Alertness, Brawny, Danger Sense, Double Shot, Steady Hands,

Potential Hindrances

Code of Honor, Curious, Loyal, Quirk (Annoyingly chipper, Vegetarian, Collects interesting rocks, Always barefoot), Poverty

Gear

$1000

Powers

None

Hill Dwarf Treasure Hunter

Stats

Agility D6

   Fighting D6

   Lockpicking D8

   Shooting D4

   Stealth D4

Smarts D6

   Healing D4

   Notice D8

   Taunt D6

Spirit D6

   Intimidation D6

   Persuasion 0

Strength D6

Vigor D6

---

Charisma 0

Pace 5

Parry 5

Toughness 5

Potential Edges

Artifact Hunter (Racial Edge), Alertness (Racial Edge) Brawny, Danger Sense, Scavenger, Strong Willed

Potential Hindrances

Bloodthirsty, Cautious, Code of Honor, Loyal, Stubborn

Gear

$1000

Powers

None

Mountain Dwarf Merchant

Stats

Agility D6

   Fighting D8

   Lockpicking 0

   Shooting 0

   Stealth D4

Smarts D6

   Healing D10

   Notice D4

   Taunt D4

Spirit D8

   Intimidation D4

   Persuasion D8

Strength D6

Vigor D6

---

Charisma 2

Pace 5

Parry 6

Toughness 5

Potential Edges

Arcane Resistance, Improved Arcane Resistance, Attractive, Very Attractive,

Charismatic, Connections, Healer, Nobel,

Potential Hindrances

Cautious, Code of Honor, Hard of Hearing, Overconfident

Gear

$1000

Powers

None

Ice Dwarf Tank

Stats

Agility D6

   Fighting D12

   Lockpicking 0

   Shooting D6

   Stealth D4

Smarts D4

   Healing D4

   Notice D4

   Taunt D4

Spirit D4

   Intimidation D4

   Persuasion 0

Strength D8

Vigor D8

---

Charisma 0

Pace 5

Parry 8

Toughness 7 (+1 racial bonus)

Potential Edges

Brawny, Nerves of Steel, Sweep, Trademark Weapon

Potential Hindrances

All Thumbs, Arrogant, Bloodthirsty, Clueless, Heroic, Outsider, Poverty, Ugly

Gear

$1000

Powers

None

Human Martial Artist

Stats

Agility D8

   Fighting D12

   Lockpicking 0

   Shooting 0

   Stealth D8

Smarts D4

   Healing D4

   Notice D4

   Taunt 0

Spirit D6

   Intimidation 0

   Persuasion D6

Strength D6

Vigor D6

---

Charisma 0

Pace 6

Parry 8

Toughness 5

Potential Edges

Acrobat, Ambidextrous, Brawny, Extraction, First Strike, Florentine, Martial Artist, Quick, Sweep, Two-Fisted (If using Hindrance points to raise Strength, can also take Brawler)

Potential Hindrances

All Thumbs, Arrogant, Bad Eyes, Coe of Honor, Death Wish, Heroic, Loyal, Pacifist, Poverty, Wanted

Gear

$1000

Powers

None

Human Mage

Stats

Agility D4

   Fighting D6

   Lockpicking 0

   Shooting 0

   Stealth D4

Smarts D10

   Healing D6

   Notice D8

   Taunt D6 Magic D10

Spirit D6

   Intimidation 0

   Persuasion 0

Strength D4

Vigor D6

---

Charisma 0

Pace 6

Parry 0

Toughness 0

Potential Edges

Arcane Background Level Headed, New Power, Power Points, Wizard

Potential Hindrances

Anemic, Arrogant, Cautious, Curious, Mean, Overconfident, Phobia, Quirk

Gear

$1000

Powers

Detect/Conceal Arcana (free action), Elemental Manipulation (free action)

Pick 3 from: Armor, Blind, Bolt, Boost/Lower Trait, Burrow, Burst, Confusion, Darksight, Deflection, Entangle, Environmental Protection, Fear, Healing, Light/Obscure, Smite, Speed, Stun, Succor, Summon Ally

Power Points 10

Human Ranger

Stats

Agility D8

   Fighting D6

   Lockpicking D6

   Shooting D6

   Stealth D6

Smarts D6

   Healing D6

   Notice D6

   Taunt D6

Spirit D6

   Intimidation D6

   Persuasion D6

Strength D6

Vigor D6

---

Charisma 0

Pace 6

Parry 0

Toughness 0

Potential Edges

Alertness, Ambidextrous, Brawny, Danger Sense, Extraction, First Strike, Fleet Footed, Quick, Two-Fisted,

Potential Hindrances

Clueless, Code of Honor, Heroic, Illiterate, Loyal, Outsider, Phobia, Poverty, Quirk, Stubborn

Gear

$1000

Powers

None

Human Mercenary

Stats

Agility D8*

   Fighting D8

   Lockpicking D4

   Shooting D8

   Stealth D4

Smarts D6

   Healing D4

   Notice D6

   Taunt D4

Spirit D6

   Intimidation D4

   Persuasion D6

Strength D8*

Vigor D6

---

Charisma 0

Pace 6

Parry 0

Toughness 0

Potential Edges

Brawny, Florentine, Quick, Rich, Sweep

Potential Hindrances

Arrogant, Big Mouth, Code of Honor, Greedy, Heroic, Overconfident, Quirk, Stubborn

Gear

$1000

Powers

None

Character Sheets

I used the character art as a background image and styled the various stat blocks and normal character sheet info into semi-transparent blocks as a web page. Then I converted these to PDF using Firefox (which allows me to include background images when printing, including printing to PDF).

If I've properly figured out how to share stuff on Google Drive you can see an example character sheet. This happens to be the character played by my son, the only magic user chosen.

Custom Fantasy Races and Pre-Generated Characters

Rules for making your own Races can be found in the Fantasy Companion and I think maybe in the newest version of the core rules as well. The gist is you've got a list of modifiers both positive and negative. As long as each Race balances in terms of net "points" then the relative power level should be fairly even.

I (rightly) assumed these kids would be guided by visual cues. I also assumed among common fantasy creatures Dwarves and Elves would be the easiest to locate in terms of character art. Making 3 variations of each and tossing in standard Humans gives me 7 playable Races, which is plenty for my purposes.

Speaking of character art, Google image search is your friend. And I was pleased to discover Wizards of the Coast is kind enough to provide Art Galleries and Art & Map archives. Had I known that going in I could have made Savage versions of some more oddball races like Tieflings or Warforged. But I made the Races and pre-gens before I went hunting for character portraits.

I think in my previous entry I said the Races balanced out at +2. That's wrong. My Races are +2 above the norm, which is already +2, so everything balances out at +4 per the Race creation rules.

Humans

Basic, adaptable, fantasy Humans. They get 2 free Edges during character creation at +2 points each for a total of +4. Easy peasy.

Moon Elves

Natural born assassins based on the Drow but with none of the assumed evilness of that Race.

• Base Pace 10 (+2)
• Infravision (+1)
• Wall Walker (+1)
• D6 Agility (+2)
• Allergy: direct sunlight (-1)
• -2 Charisma, generally creepy (-1)

Sun Elves

Traditional "high elves" with a natural affinity for magic.

• +10 Power Points (+2)
• Flight: Innate magical levitation (+2)
• Elemental Tolerance: +4 to resist negative environmental effects (+2)
• -1 Toughness (-2)

Wood Elves

Legolas clones. (Hey, I never claimed originality was my goal here!)

• Keen Sight (+1)
• Free d6 in Shooting (+1)
• Level Headed Edge (+3)
• Quirk: Fatigue roll if sleeping in any "unnatural" environment (-1)

Hill Dwarves

Adventurers and treasure hunters. Indiana Jones wearing a ZZ Top beard.

• Artifact Hunter (+2)
• Free d6 Lockpicking (+2)
• Alertness (+2)
• Pace 5 (-1)
• Greedy (Minor) (-1)

Mountain Dwarves

Closer to traditional Dwarves, but not at all isolationist as Dwarves are sometimes portrayed and with a dash of the natural inclination towards merchantile trade usually found in Gnomes (or Ferengi).

• +2 Charisma (+2)
• Free d6 Spirit (+2)
• Free d6 Persuasion (+1)
• Pace 5 (-1)

Ice Dwarves

Ferral warriors of the north. I think I stopped short of giving them all horned helmets.

• Hardy (+3)
• +1 Toughness (+2)
• +4 resist cold (+1
• -4 to resist heat (-1)
• Pace 5 (-1)

I had planned to include my pre-generated character ideas in this same post but checking the preview I see this is already quite a lot info so I'll stick a fork in this one and save pre-gens for next time.