Deep Purple cruise videos

We took the boat “Deep Purple” out for a cruise on Sunday and took some short video clips here is the result. If you like classic diesel engines you will love the sound on these.

Deep Purple is powered by a Kelvin K2 made in 1944.

 

Posted in Randomness | Comments Off on Deep Purple cruise videos

SQL query speed

Take a look at these 2 queries

Query 1:

SELECT if(TIMESTAMPDIFF(SECOND,dtime,NOW()) > 90,0,counter) AS counter,
UNIX_TIMESTAMP(dtime) AS dtime
FROM ifInOctets_1
WHERE id = 100
ORDER BY dtime DESC
LIMIT 1

Query 2:

SELECT if(TIMESTAMPDIFF(SECOND,dtime,NOW()) > 90,0,counter) AS counter,
UNIX_TIMESTAMP(dtime) AS utime
FROM ifInOctets_1
WHERE id = 100
ORDER BY dtime DESC
LIMIT 1

Both produce the exact same result, 1 row with a unix timestamp and a counter. There is only 1 letter difference between the 2, but query 2 is orders of magnitude faster. With just under 100k rows in the table query 1 took 0.3240 sec and query 2 took 0.0012 sec thats 270 times faster and as the table grows the difference will increase. 

Lets have explain tell us why

Query 1: Using where; Using filesort used index to get id but needed to sort 100K rows to get the most recent row

Query 2: Using where used index to get the most recent row and only had to look at 183 rows to find the id

So why did it not use the index on query 1? Thats simple dtime is a datetime field but it was modified to be a unix_timestamp in the query and that does not match the index type, that caused the query optimiser to use the id index.

You might think that 0.3 seconds is still quite fast, but this query is used to build an image every minute and uses 246 queries, thats 74 seconds just to get the data and it still has to produce the image. With query 2 all 246 queries take less than 0.3 seconds.

Spend your time optimising your SQL it makes much more difference than you think.

Posted in SQL | Comments Off on SQL query speed

Where did the day go?

Feels like it was only a few hours ago that I got out of bed. Quick trip to the boat to get some cable I needed for the surround sound system at home, grab a bottle of Hendricks at Waitrose, restock veg (onions,spuds and dirty carrots) at Greenfields and it’s dark already.

I was planning on cooking a chilli for dinner but it is way to late to start that now. Looks like it will have to be a takeaway.

Not even started on the speaker cables yet either.

Tomorrow needs to last a bit longer.

Posted in Randomness | Comments Off on Where did the day go?

Impressed

New TV for the boat has arrived, text from DPD saying it would be here between 10 and 11, it arrived just after 10:30 nice to see a courier getting it right.

The TV itself has a good picture, but you would expect 1080P to look good at 22″ will see how good it is when its connected to a decent ariel at the weekend. One thing I do notice is the power consumption, the amazon page says 22 watts the packaging says 17 watts with a peak of 35 watts. I will take a guess that just watching TV is 17 watts and using the dvd player is 35 due to the motors.

Spinning media is so last decade.

On the subject of spinning media, the hard disk in my iMac at work finally gave up yesterday. Now replaced with a shiny new SSD and what a difference that has made, 2006 iMac feels like a new machine almost as quick as my late 2012 Mac Mini at home. It has got me wondering how much faster the Mac Mini would feel if it has SSD instead of the spinning disk.

The most annoying bit about changing disks is restoring from time machine, I had to do a full reinstall of Mountain Lion to create the rescue partition correctly, just booting from DVD and trying to restore from there failed as it could not create the required partition. Also my dropbox account details were not backed up and MS Office needed to have its key typed in again. So far thats the only problems will see what else I find out during the day.

Posted in Apple, Narrowboat, Randomness | Tagged , , , | Comments Off on Impressed

Fixing boat annoyances

Have been fixing the annoyances discovered while on sumer cruise.

The beeping inverter:

Can I find the manual for it anywhere? of course not, that model is discontinued by Maplin and it is about 5 years old so I decide to replace it. Looked at all the available options in the local Maplin and went for a 600 watt unit. I also noticed that on the other 300 watt units they state they are only good for 150 watts it used in a lighter socket (how would it know?), we had a failed 600 Watt unit on the boat when we purchased her so I already had all the wiring loom ready to go. Nice easy installation, and I now have the 5 mains sockets available at the flick of a switch. No more bleeping and the manual with all the error codes is in the boats equipment folder so I wont lose it.

Now for the TV signal:

A12KFWell first off I replaced the omnidirectional arial that we were using with a directional one that says its designed for digital and has a built in 20dB amplifier (thats another wall mounted box I can remove), wow what a difference. Retuned the TV with the new arial just resting on the roof and it found lots more channels even some marked as HD and better than that channels that used to breakup were nice and crisp. I had also purchased an extending pole from B&Q it is supposed to be for a paint roller (that means cheap) but its 3 sections mean it extends up to 2.5 metres and its aluminium so will last too. Now after installing the arial on the pole and mounting it to the boat with some 28mm pipe clips to the cratch  board I discovered the real reason for the lack of signal. The aerial connector on the back of the TV is flaky, so out with a screwdriver to look for the bad connection. I should be that lucky, the bad joint is inside the shielded metal box the ariel connector lives in and it is soldered up solid no way to get in there with the kit I have.

That means I need to find a 12 volt Freeview decoder or replace the TV. I don’t really want another box to mount so I have ordered a new 22 inch 1080P HD TV with a built in DVD player and it runs on 12 volts and only uses 22 watts. The old TV was a 16 inch standard def and sucked 50 watts, if you own a boat you know every watt counts.

New TV should be here this week so I now have something to do next weekend. Oh and don’t worry the old TV is not getting dumped, Elianne does not have a TV in her room right now so it will be used there.

Posted in Narrowboat, Projects | Tagged , | Comments Off on Fixing boat annoyances