Improving the performance of REGEXP queries in MySql

Say we’re trying to query a table of UK postcode and just want to return the postcodes in a particular area(the Birmingham area B for instance). A naive implementation of this may be something like this

SELECT * FROM Postcodes WHERE Postcode LIKE 'B%'

This works for some postcodes but doesn’t work for this particular example because it also returns any postcodes in the BA, BB, BT etc areas. So it looks like a regular expression is required, to ensure the area code is followed by a number, so we try the following

SELECT * FROM Postcodes WHERE Postcode REGEXP'^B[0-9]'

This works and returns what we expect but it is much slower than the LIKE query. So is there any way to speed it up? Actually, it’s pretty straightforward, just combine the LIKE and the REGEXP queries, like so

SELECT * FROM Postcodes WHERE Postcode LIKE 'B%' AND Postcode REGEXP'^B[0-9]'

This give MySql the chance to first filter the data based on the LIKE clause then only use the regular expression on the filtered data. It’s not quite as fast as the original LIKE implementation but it’s much quicker than REGEXP on its own and unlike the original LIKE implementation it actually works.

The Fibonacci Sequence

For Christmas 1987, my brother gave me ‘yet another interesting book’ (his words), “The Penguin Dictionary of Curious and Interesting Numbers”. Clearly it must be pretty interesting, since I was surprised to see it is still available. Being something of a maths geek, I did enjoy reading it, but it has been languishing in my loft for a few years now.

Jump forward a couple of decades and I was reading “The Rabbit Problem” as a bedtime story to my daughter and was wondering why it was set in Fibonacci’s Field. Clearly this was related to the Fibonacci Sequence (and reading the back cover gave the game away) but I was unsure how the Fibonacci Sequence was related to rabbits. So time to climb up into the loft to find out. Turns out the Fibonacci Sequence was the solution to a problem about rabbits breeding. And it also appears a lot in nature, specifically in the number of petals etc in plants.

As if that wasn’t enough, the ratios of successive terms of the Fibonacci Sequence tend towards the Golden Ratio, another interesting number that you can find out about in the dictionary. And here’s a little Javascript example to show that convergence.

Free XML sitemap generator with unlimited pages

I’ve been happily using this free XML sitemap generator for a while, but as my sites have got bigger I keep hitting the 500 page limit. Since I’m too tight to actually pay for this kind of service I’ve been searching around for a while for another free alternative. I was even thinking of writing my own, but before I was forced down that road, I found this alternative generator. I’m not too keen on Java applets generally, but this one seems to work very well. If it fails to download any pages, you can go back and retry downloading the failed pages. It also gives details of how long pages take to download, so it can also be a useful performance checker of a website.

Even so, I’m a little ambivalent towards sitemaps, especially if they are generated using one of these tools that just crawl your website to find all the pages. What information does this provide to search engines that they can’t find for themselves by crawling the site themselves? And If I do want to provide any more useful information, I have to edit the thing by hand, which I really can’t be bothered doing.

Google Maps elevation API

Version 3 of the Google Maps API adds a new dimension to maps, the elevation of locations. I did plan to knock together an example of how this works, but the example provided by Google is pretty good itself.

I think this new API could be very useful. The obvious use would be when planning a walk or a bike ride and wanting to get an idea for the terrain, but I’m sure there are many more uses.

Google Maps styled maps

Version 3 of the Google Maps API adds support for styled maps, which means it is now possible to hide features or display them differently on the base map. This could lead to some interesting customisations, and also leads to some questions about how exactly Google have implemented this? Are they creating map tiles on the fly? Or have they stored every possible permutation of styling? Surely the former, but how come it doesn’t seem to make any difference to the performance of rendering?

Anyway I had a play with the styled map wizard (which doesn’t work in IE for some reason) and thought I’d experiment with a map displaying just rail lines. And I kind of got this working, but there was a fundamental problem. Rail lines are only displayed when the map is zoomed in below a certain threshold so the map wasn’t any use to get a broad overview of rail lines in the UK for instance. And the styled maps API doesn’t seem to provide any control over this thresholding.

So it looks good as an initial implementation, but I think more control is required to be a completely useful API.

Google Maps geolocation API

Google have had an API to get the user’s location for a while (via the Google loader), but I didn’t think it was very good. But now there are two new methods of figuring out the user’s location, one using the new W3C standard and the other using Google Gears. The good news is they seem to do a very good job of locating the user (scarily so in fact), the bad news is that neither are supported by Internet Explorer or Safari.

Google Chrome uses Google Gears to do the job, FireFox uses the W3C method and frankly I’m not too bothered what Opera supports, since so few people use it.

One annoyance is the prompting you get in the browser, although this is fairly understandable since this information could potentially be used for evil. And it won’t be an especially useful feature until it’s adopted by more browsers. But a combination of all three methods may produce a reasonable compromise. 

Google Maps geocoding still sucks

I spent a little time today starting to convert some of my pages to the Google Maps API version 3. At the same time I thought I’d convert my geocoding to use the latest version since I was sure it would have improved since it was added to the API and failed to work too well three years ago. But no, it still sucks big time for postcode geocoding, as this test page demonstrates. But what is strange is that the GlocalSearch class still does a fine job of geocoding. Same company, different APIs and different results. This can’t be down to the Royal Mail throwing their toys out of the pram since the Ordnance Survey now provides geocoded postcode data for free so I assume it’s down to Google stuffing up.

Converting to Google Maps version 3

Google Maps API version 3 has just moved from the Google Labs to live so I thought I’d try converting a simple version 2 map to version 3. This is what the version 2 code looks like

  <script src="http://maps.google.com/maps?file=api&amp;v=2&amp;key=ABQIAAAAjtZCgAx5i04BiZDO6HlxhRQUdBDpWCOMRMbgTcqadX0jQ8HOERSxXxhk24TIBUpivovAKLrnpSio9w" type="text/javascript"></script>
  <script type="text/javascript">
    window.onload = function () {
      if (GBrowserIsCompatible()) {
        var map = new GMap2(document.getElementById("map"));
        map.setCenter(new GLatLng(51.49506473014368, -0.130462646484375), 10);
        map.addControl(new GSmallMapControl());
        map.addControl(new GMapTypeControl());
        map.enableDoubleClickZoom();
        map.enableScrollWheelZoom();
        var layer = new GGeoXml("http://www.doogal.co.uk/LondonStationsKML.php");
        map.addOverlay(layer);
      }
    }
  </script>

And this is what the version 3 code looks like

  <script src="http://maps.google.com/maps/api/js?sensor=false" type="text/javascript"></script>
  <script type="text/javascript">
    window.onload = function () {
      var latlng = new google.maps.LatLng(51.49506473014368, -0.130462646484375);
      var options = {
        zoom: 10,
        center: latlng,
        mapTypeId: google.maps.MapTypeId.ROADMAP
      };

      var map = new google.maps.Map(document.getElementById("map"), options);

      var georssLayer = new google.maps.KmlLayer('http://www.doogal.co.uk/LondonStationsKML.php');
      georssLayer.setMap(map);
    }
  </script>

The first thing I noticed is that the API key is no longer required which is very welcome, since it makes everything simpler (no more hassle when moving scripts between domains or trying to debug somewhere other than the domain or localhost). I guess the namespaces are a good thing and the class names seem more memorable. At this point I’m not sure if the API is any better than the old one, I’ll have to do some more digging before I can make a decision. But it does look like there’ll be quite a bit of work to convert old sites to the new API. Not that we’re forced to upgrade of course, at least for the moment.