Sam's World Of No

Relational databases like MySQL rely on correct indexing of tables in order to function quickly. In fact, adding proper database indexes is often one of the biggest improvements you can make to any application, including Rails applications.

A few well-known blog posts have dealt with this subject, giving excellent advice on how to index your tables for best performance. Jamis dealt with the subject a while ago, and recently Matt Jankowski has posted a fairly exhaustive guide. I felt that this excellent guides could be built upon, and the analysis that I would do, given a query to index, could be automated.

Enter Ambitious Query Indexer. Unlike other plugins in this space, AQI (I’m starting to shorten it to ‘aqute’; not sure if this will stick) doesn’t optimise queries on the fly by reducing eager loading, and it doesn’t look at tables in an attempt to guess what indexes might be needed to query it.

AQI’s approach is to analyse your Rails app, work out what queries it will run during its operation and spot any indexes that could be added for the MySQL engine to use. Why is this a good approach? Simply, because it focuses on the queries you run, not the tables they run upon. Imagine the following story:

1. You have an Article model containing a user_id field in an articles table
2. You have a User model that has_many articles
3. Calling User.first.articles will run a query like “SELECT * FROM articles WHERE user_id = ‘1′”.

How do you speed this query up? You would add an index to the articles table on the user_id field. Happy days.

Now imagine you install acts_as_paranoid on the Article model or something similar – a plugin that overrides the default finders – suddenly, your query will be something like:

SELECT * FROM articles WHERE user_id = '1' AND (deleted_at IS NULL or deleted_at < '2009-10-05 22:07:00')

Whoops - your index doesn't work any more because you've added a field to the query. Your database is about to go to the dogs, and only a cursory look at the table wouldn't reveal why because it's not immediately obvious that you now need a compound index on user_id and deleted_at.

Unfortunately, when it comes to indexing, looking at a table to work out what indexes you need is not enough. Adding indexes during your migrations won't always cover you, and best practice indexing won't fly on most real-world applications. You have to look at the queries that interrogate that table. AQI will do this; it will find, run and analyse your queries and give you a list of indexes that will improve performance. Yay!

To install, follow the easy instructions on GitHub - Ambitious Query Indexer. This plugin is still under active development and I'm adding new features all the time. Please let me know any feedback you have, or any issues you encounter.

Bookmark:

Even with the iPhone app, I’m not sold on Spotify Premium. £10 is too much. £5, I’d consider it, but £10 is too much when the music is never really mine. Fair play to the guys, though, the streaming quality is spot on, the cross-platform is impressive and the interface is so good that I often listen to music I have on iTunes, on my hard drive, on Spotify instead.

But £10 is too much, so for now I’m going to be listening to the following terrible adverts:

The Right Livelihood Award

I’ve never heard of this award, and I’m sure it’s all for a very good cause. But the advert is some shonky stuff. It starts with a vocal stumble when trying to say ‘Right Livelihood Award’ (never a good start to fluff the brand!), and then goes to tell me absolutely nothing about what the award is or does. But apparently there’s one thing I should remember – that we can all do something if we try hard enough, that wishing and dreaming make crazy things happen, and that you should never eat yellow snow. Or something like that – the point is, this ‘one’ thing I should remember is divided into three meaningless clichés, and there’s no chance I’m remembering them.

The whole thing sounds like it was recorded outside on a busy street. And still I’m none the wiser about what this doubtless very worthy award is for!

ShareMyPlaylist.com/ShareMyPlaylists.com

Nice idea; social playlists for spotify. Presumably a feature that spotify will make themselves eventually, but anyway, what I love about this advert is the girl talking gets the domain name wrong. It’s www.sharemyplaylists.com (playlists plural), but she twice calls it www.sharemyplaylist.com (playlist singular). The latter domain is a most certainly not a production ready website – in that it’s a file index with a couple of favicons and a robots.txt file. Fail!

Crucial Memory

Your computer has slowed down – do you need more memory, or just to be patronised by the ‘computer doctor’? Apparently, with crucial, both are possible. Joy.

This one seems to have buggered off for now, but it sure was odious when it was about – and for one 48 hour heavy work weekend it seemed to be the only advert available.

MTV safe sex ultra-liberal moralising

‘Everybody loves music… especially in the bedroom’. What?! I prefer music in my study. Or at my piano. Quite the impressive segue though. This MTV ad, featuring some beautiful Irish bird, suggests that I should refrain from unsafe sex and enjoy ’sexy playlists’ in the bedroom instead. Not entirely sure that this is the best way to reach ‘young people’.

That said, I still chuckle to myself when this ad comes on; just once I wish she’d say ‘Everybody loves sex… especially in the music room‘. Then I’d definitely never pay for the premium subscription.

Rhino Vinyl

‘Spotify is ace; streaming, high quality, distributed. At Rhino, we love it almost as much as vinyl records’. Say again?You like mp3s, but really you like vinyl and think mp3 is shit? Again, quite the segue and as the advert rolls on, it does seem to make sense. But I love the transition; it’s akin to local news shifting between a murder case and a human interest piece about puppies and their hilarious habits.

Other favourites include the advert for Scarlett Johansson’s single getting cut off half way through every single time, and Amy McDonald, who talks for what seems like hours about how, somewhere, live tracks and her whole album is available to listen. It might be on Spotify – it probably is – but she seems to neglect to mention it!

And just as I write, Adam from Spotify has used the Spotify recording microphone, bought for £7.99 at Argos, to record an ad telling me how good their ad targetting is. Touché!

Bookmark:

So, all of a sudden, we’re all addicted to Twitter. I can follow my favourite female singers, have ladies who like to lounge in their underwear follow me. I can bitch about a company and all of a sudden their customer experience manager is on my case, trying like hell to sort my problem out so I tweet about how good their service is. And this is great – I get my problem sorted quicker.

It’s fair to say that Twitter’s influence is, in some spheres, pretty large. TechCrunch gets more traffic from Twitter than from Digg. That’s pretty impressive, considering the amount of time that people still spend gaming Digg. But its influence is still pretty limited. As a rule, it’s still fairly restricted to the technology industry. It’s not that easy to use, and as replies, retweets and hashtags take over, it’s becoming more and more like a raw message format than something that’s human readable. URL shortening services are hardly accessible to the lay person.

I think we’ve all tried to explain what twitter is to a friend, even a tech-savvy one. Perhaps we’ve got as far as getting them signed up and an iPhone client downloaded. Three months later, they haven’t updated it. They don’t understand what it’s for, and they don’t have any followers. People who abandoned MySpace and Bebo for Facebook  in a heartbeat are not migrating to Twitter in the same way.

Twitter is still a niche product. So, if it does become the ‘pulse of the planet‘, is its current utility as a community management/traffic driving/networking/brand management/corporate communications/customer service tool going to be limited? I think it might be.

I follow 119 people – and something like 20 of these tweet on any sort of regular basis. Even that is a bit much. I had to unfollow Jonathan Ross and Zeldman; I couldn’t hack it. More than 20 new tweets in an hour and I’m screwed. How am I going to cope if every tweet that mentions a company has an @reply from that company’s community management being dead helpful? I’m not. If there a billion people on twitter, I’ll be swamped and I’ll have to leave.

More to the point, how am I going to get that personalised service if said community managers are inundated because there are a billion people on twitter? Think of the companies with REALLY dreadful customer service, like O2 and BT. How are they going to keep up with the moaning? They couldn’t, unless the metric/filtering stuff gets really clever. So there goes my personal service.

So perhaps we should view the current state of Twitter as a VIP customer service and get-satisfaction-esque issue resolution tool available to geeks who can understand it. Will every moan and question get dealt with if the user base multiplies? No, unless the very basis of the whole idea changes. There’s a reason that all of these companies have taken their phone numbers of their sites and instead installed automated customer service systems/mantraps – individual interaction with the whole customer base just isn’t possible.

Will Dell be able to post and get a conversion on real-time offers of limited product lines/refurb products, if the user base was massively bigger? Not under the current model – the offers would be snapped up so quickly that people would stop bothering to click on the links.

What of real time web? What of it. Whilst aggregating tweets is a good way to find out news really quickly, it’s a job for a computer, not a person. I can’t have more than one or two ‘news’ (be it world affairs, the BBC or something on formula 1) users in my follow list; anything more is information overload. I rely on power users to filter the real time web down for me, again in real time, so that it’s just enough information to digest.

So perhaps the future of Twitter under the current model is stronger groups/networks that are reliant on real-world connections, mixed with a lot less business interaction and a selection of a very few, very important, power users.

Sounds like a pretty shaky business idea to me. No doubt, I’ll be proven wrong.

Bookmark:

Lately I find myself using a lot of paper at work – this is partly because a new PM at a client produces a lot of the stuff and partly because I fancied a change from a lot of notes on computer/iPhone. Let’s face it, there’s not a whole lot more usable than a pen and paper.

Previously I have spurned pen and paper because it feels very messy and can be difficult to keep track of. All of a sudden your centralised task list is split over various scraps of whatever you could lay your hands on at a particular moment, and my Backpack isn’t seeing the stuff it should be.

My new solution is unbelievably simple but still seems to have turned a few heads.

1. On any piece of paper, anywhere (spec document, chart, graph, list of tasks), draw an empty circle next to any ‘actionable’ item. What do I mean by ‘actionable’? I mean anything that you would miss if this piece of paper was lost. A lot of notes you make you will never read again; they are just to help you think. For everything that you need to do something on, draw a circle.
2. Once the item next to the circle has either been completed or graduated to another list, fill in the cirle.

Yep, that’s it. This means that any piece of paper I may have on my person may have these little circles on them. Once I’m done reading a particular document, or I’m back at my desk, I complete the tasks I can (in five minutes or less, GTD style), and graduate any items that can’t to Backpack. Once the paper has no empty circles, file it or (better) throw it.

This idea is not new by any stretch, and it’s certainly not anything clever, but it does help me keep things in order. The key to it really is the graduation. If you force yourself to keep all pieces of paper with a pending task or piece of information floating around (most likely on your desk), you will quickly go mad.

The other key is to have a ‘buck stops here’ list – that’s what Backpack is perfect for. Its email dropboxes also mean you can add stuff directly to this last-stop list easily. For me, the best thing about the dropboxes is that I can send tasks straight from my phone. This means that whilst I lay awake at night remembering all the stuff I need to do, I can send off a series of emails and turn my mind to other things like going to sleep.

Who knows, maybe Backpack is helping my insomnia!

Oh, and do yourself a favour when trying the pen and paper system – buy a nice pen, nice paper and instigate a recycling bin under your desk.

Bookmark:

This is under the category of ‘useful stuff I’ve found which might save someone else some googling’:

1. Log in to Facebook
2. Add the Twitter Facebook App
3. Register your Twitter account with said app
4. Click ‘Allow Twitter to update your Facebook status’
5. Click yes to Vista-esque security settings to allow Twitter access to your Facebook status

Done!

Should you later want to stop Twitter updating your Facebook status:

1. In Facebook, hover over ‘Settings’ and choose ‘Twitter Settings’ from the menu
2. Click on ‘Additional Permissions’
3. Uncheck ‘Update my status without notifying me.’

NB: Apparently @reply tweets won’t turn up. Probably for the best; they wouldn’t make a whole lot of sense!

Anybody else think that with all the @, # and RT: syntax, Twitter is turning into an obfuscated language of its own? It reminds me of the weird format we used to have to send to roaming GPS boxes, back when I worked on a project that involved involved such things, and debugging problems with the message syntax got really quite difficult!

Bookmark: