MySQL Is Driving Me Insane – GROUPed VIEWs and Multi-Table UPDATEs

I was pretty happy to discover that the latest releases of MySQL (5.0+) allow for creating VIEWs and offer improved subqueries. I’ve been trying to incorporate some of this into my latest little game project, but a few irritations in MySQL are making it a miserable process.

The biggest problem is that the new MySQL VIEWs automatically resort to TEMPTABLE if you throw a GROUP BY into a query. As a result, it’s impossible to create certain types of VIEWs on very large tables, because your logical WHERE restriction on the VIEW behaves as if you issued GROUP BY…HAVING on the underlying query instead of WHERE somerestriction[s]…GROUP BY. If your query would return 10,000 rows (yes, I know that’s only relatively “very large” but I need to keep my query result times in the sub-second range and I only need 32 of those rows) before HAVING is applied, well, that’s what you get with the VIEW. For example:

CREATE VIEW myview AS SELECT foo.ID FROM foo_table foo GROUP BY foo.bar;

In that little snippet, you’d think that you could “SELECT * FROM myview WHERE foo.ID = 1” and restrict the result set, but the WHERE restriction is applied as a HAVING. This is really, really unfortunate, as it makes the VIEW painfully, unusably slow when you have more than a tiny handful of rows.

The second annoyance is that while MySQL supports multi-table UPDATEs, it behaves in an illogical (to me) fashion when trying to do the following:

UPDATE foo JOIN bar ON somecondition SET foo.somefield = foo.somefield + 1;

The logical behavior seems to me to be that for each “bar”, “somefield” would get incremented by 1. However, in practice, MySQL updates “somefield” once and ignores the rest of the results returned by the “bar” JOIN. It’s almost the same problem discussed here and here, but dumping/reloading the tables seems irrelevant (as does my MySQL version – 5.0.22 currently) and I seem to be bumping up against a restriction briefly mentioned at the bottom of the MySQL “UPDATE Syntax” documentation page – “Currently, you cannot update a table and select from the same table in a subquery.” [Emphasis mine] I’m not the only person to have raised questions about UPDATE’s odd multi-table behavior.

I was hoping to use something like the second to mitigate the problems created by the VIEW shortcomings, but that doesn’t seem to be happening. The best solution I’ve encountered seems to be to use group functions in a subquery as per:

UPDATE foo
(SELECT SUM(somevalue), id FROM bar WHERE somecondition GROUP BY something) bar_alias
SET foo.somefield = foo.somefield + bar_alias.somevalue
WHERE foo.id = bar_alias.id;

That’s much less elegant and less flexible than using either an UPDATE…JOIN myview or UPDATE…join_query which I’d hoped to be able to do.

Here’s to hoping that the VIEW gets optimized better for the future. It would definitely make my life easier to be able to use GROUP BY in a VIEW without choking up my system. Or maybe “Currently” will become “Long ago” and I can use self-joining table UPDATEs, which is better than nothing.

And if you happen to know any way around either of these that doesn’t involve eliminating the GROUP BY on a VIEW or using a SUM() on the UPDATE, please tell me!

Still Alive!

Just a small update to say that yes, despite rumors to the contrary, this blog is still alive.

What’s kept me busy? My Earth:2025 rewrite is coming along nicely – hopefully I’ll have a decent beta version within the next 6-8 weeks. My 3rd novel is just shy of halfway done, and I’ve been working on a few songs whenever I can spare a couple of moments. And *fingers crossed*, I’m hoping to test for 4th kyu in aikido as soon as late August.

Amid all of that, I’ll make an effort to write something interesting soon. I promise!

Tech Tip: ext2fsd and WinXP

If you’re, like me, one of those people who dual boots Windows XP + Linux, you might have discovered ex2fsd, which allows you to access your ext2/ext3 volumes while you’re using Windows. Recently, I suddenly found myself unable to access my /home/nate/Music directory on my ext3 volume which had previously functioned properly. Windows started telling me that it was a “location that is unavailable,” which made no sense. After a few reboots and doublechecking in Linux that the directory was there and functioning properly, I noticed that I had a /home/nate/music text file in the same directory. As Windows doesn’t cleanly differentiate between “music” and “Music” in terms of case, it was unable to open the Music folder because it was apparently getting the “music” text file.

So if you find a folder is suddenly unavailable under Windows, make sure that you didn’t create a file with the same name in the parent directory on your ext2/ext3 volume. Windows is pretty inflexible that way.

Thoughts of the Day

Kaavya Viswanathan got six figures for writing a story about a geek who needed to party more in order to get into Harvard, or something like that. Given the scope of the plagiarism (at least 40 documented instances, according to Random House) scandal that has hit the news, this is really depressing somehow. Apparently if I really want to be successful as a writer, I need to do more schmoozing and less writing, and if I have to plagiarize a bit to get there, aw shucks.

Nintendo Revolution got renamed to the Nintendo “Wii.” Prounounced “We.” So, wanna come over and check out my Wii? How about going to the video game store and asking the clerk if he’s got a Wii, and what he thinks of it? And your Wii is multiplayer, too. Isn’t that awesome?

Starfish are edible. That rocks. Next time I go to the beach, I am gonna try some starsushi. You heard me.

Microreview: Lacuna Coil’s Karmacode

I’ve been a fan of Lacuna Coil for a couple of years now, but wasn’t hugely impressed with their last album, Comalies. So when Karmacode was released this month, I found myself surprised to be quite addicted. This album has more in common with their earlier work than with Comalies, yet injects some fairly interesting sounds and rhythms that are a little more playful than some of the plodding, measured songs.

The highlight of the album has to be their cover of Depeche Mode’s “Enjoy the Silence,” though. I never liked the original mix much, given that I’m not a fan of the over-reverbed, electronic style of that mix. This one injects some Lacuna Coil metal into it while staying true to the original, so it’s eminently listenable. I’ve been playing that one song more than any other from the album for a few days now.

If you’re new to Lacuna Coil or goth-tinged metal, I’d recommend this album as a good starting point. If your only exposure to this kinda music has been Evanescence, you’ll be pleasantly surprised to find out that Cristina Scabbia can actually sing without an autotuner. Give it a listen.

Book Review: Koushun Takami’s Battle Royale

I really cringe at buying new books these days, because my attention span is too limited to bother re-reading most novels and therefore whatever I pay is for the one-time experience. It’s not that I’m unwilling to shell out $20 for a book, but rather that I hate to do so for a book I might not read ever again.

Still, occasionally I find myself in a bookstore browsing, and a couple months ago, Koushun Takami’s Battle Royale caught my eye. The cover is in stark red and black, and the silhouettes of a male and female stand beside each other under the jagged title. Eye-catching, to be sure, especially when swimming in a sea of lurid, Darrell Sweet-esque covers in the fantasy/sci-fi section. So caught my eye it did, and I picked it up. The storyline intrigued me: a bunch of students on an island are forced to kill each other. I resisted buying it until this past weekend, when I also failed to resist watching Ice Age 2 (I have a weakness for Scrat the squirrel). But I digress.

In any case, the novel was definitely my type of story. Take 42 junior-high students, toss ’em onto a deserted island, randomly hand out weapons and supplies, and force them to kill each other. Oh, put explosive collars on their necks as motivation and control. Every few hours, parts of the island become off limits, enforced by the collars. Failure to play the game is a sort of no-win prisoner’s dilemma: if no students are killed within 24 hours, everyone dies.

The story largely twists and turns around how the island brings out each student’s inner motivations. From the sociopathic to the psychotic, nihilist to hero, the students all respond to the unimaginable stress in their own unique ways. To say more would spoil the joy of finding these things out for yourself, but the read is worthwhile.

Oh, did I mention that the story is relentlessly gory? If you can’t take a description of a cut throat sounding like a fresh lemon sliced with a very sharp knife, or jelly-like brains exposed from a massive head wound, then this book is probably not for you. If, on the other hand, Happy Tree Friends fills you with glee, then you don’t want to miss this story.

My largest complaint, if there’s really anything to complain about, is that the story is set in an alternate timeline where Asia’s political map is completely different and where the US is some sort of Mecca for rebellious teenagers. I realize that this may indeed be the case for people living outside of North America or Europe, but North America is rapidly descending into the sort of totalitarian, mind-controlled state that the Republic of Greater East Asia is depicted as being in the novel. There is also a small exploration of rock-as-resistance, where the “rebel” characters in the story that distrust the state use their knowledge of rock music as a way to identify each other and to communicate unapproved ideas. It’s cute, but a bit boring.

I think if you overlook the quasi-1984 aspects of the story and treat it purely as entertainment and a character study on a large scale, you’ll find Battle Royale well worth your time. It’s a novel that, for once, I’ll definitely read again.

Dodge Hates Flowers: The 2007 Dodge Caliber FlowerKiller

I hit Yahoo! earlier today looking at the headlines, and just above the news on the right, a Flash ad caught my eye. I hate it when ads catch my eyes, especially stupid ones with cheesy dancing flowers. In any case, it turned out to be an ad for the 2007 Dodge Caliber, some sort of weird car-sized SUV-shaped thing. Which was made abundantly clear by the weird car-sized SUV-shaped thing mercilessly slaughtering flowers.

While I don’t have anything against slaughtering flowers, having done it myself with sticks and lawnmowers in the past, I take great amusement in observing Dodge’s commitment to the environment in action. I have taken a screen capture for you from the ad. Behold, the 2007 Dodge FlowerKiller in Action!

2007 Dodge Caliber Killing Flowers

You may be able to watch this ad yourself for a limited amount of time – I’m not sure how long Yahoo! keeps these things up. Enjoy watching the dancing flowers perish under the wheels of a Dodge, providing a double boost in global warming: increased emissions and reduced greenery.

Movie Review: Mirrormask

I finally had the opportunity and the time to see Neil Gaiman’s Mirrormask this evening. I was rather underwhelmed by Gaiman’s novel American Gods, so I wasn’t really sure what to expect seeing Gaiman on the screen. Still, knowing his graphic novel background, I was hoping for better screen translation of his imagination than I got with that novel.

First, I’d say this movie gets a 4/5 on the strength of the visuals alone. It’s just stunning. I’m sure that people have commented on how surreal it seems at times, and with good reason. There’s a lot of allusion to fairy tale in the story, but everything is couched in imagery that could have jumped out of a painting. It’s breathtaking. If you appreciate visuals, you will love it.

The soundtrack is a weird cabaret/trip-hop fusion; I suppose this is what happens when you try to fuse circus atmosphere with the darkness that pervades the story and imagery. Funny enough, it works really well, although my girlfriend in the other room asked me if I was watching porn when she heard the soundtrack without knowing that I was watching the movie.

The biggest failing, in my opinion, was the lack of innovation with the story line. You can predict everything that happens in this movie ten minutes in advance, and there isn’t really any moment where you feel real fear or suspense. Still, the visuals are lush enough to keep you watching just to find out what you’ll see in the next scene.

The bottom line is that if you like visually stunning films, you’ll like this one. If you haven’t seen it, it’s worth your time.

Last Post of 2005 (probably)

I’m still alive, yes.

A couple of things. I’ve completely finished my aforementioned second novel, including multiple editing posts. The only thing it lacks now is a title, and a publisher. With any luck, this will make it as my first novel to be officially published. Want to read it ahead of that? Send me an email and I might send you a digital copy to preview.:-)

Secondly, after reading about the New Orleans police killing a knife-wielding man, it occurs to me that every department should stock a medieval suit of plate armor, sword, and shield. No need to kill the knife guy; your metal giant can just sit on him. And if he’s carrying a sword, I’m pretty sure your knife-wielder will just drop it. I mean, there you are, with your little six-inch knife, and some huge guy in armor comes at you with a three-foot sword telling you to put it down or lose the hand. There, a relatively inexpensive* and non-lethal way to subdue criminals who are threatening you with knives.

*compared to lawsuits

Hope all of you out there have had nice holidays. Remember, New Year’s Resolutions are for the weak. Dont’ drink too much. Or at least don’t drive if you do. Cheers!