Categories
Tips

MySQL: Are you actually utilizing your indexes?

This might seem elementary to those of you that are DBAs or something similar, but it was fascinating to find out (not to mention it greatly helped what I had to do), so I decided to post it, in case it helps someone else too. A few moments ago I found out that whereas a query along the lines of…

SELECT title, COUNT(1) as replies
FROM post INNER JOIN thread USING(threadid)
WHERE UNIX_TIMESTAMP(NOW()) - post.dateline < 86400
GROUP BY threadid
ORDER BY replies DESC
LIMIT 5

took a whopping ~10 seconds on a post table of around 2,000,000 rows and a thread table of around 40,000 rows, the following:

SELECT title, COUNT(1) as replies
FROM post INNER JOIN thread USING(threadid)
WHERE post.dateline > UNIX_TIMESTAMP(NOW()) - 86400
GROUP BY threadid
ORDER BY replies DESC
LIMIT 5

took a mere 0.03 seconds!

Probably, MySQL wasn’t able to utilize the B+ tree index of the dateline column in the first query, whereas in the second, things were a bit more obvious to it. This can also be observed by examining the information about the execution plan that EXPLAIN provides:

mysql> explain select t.threadid, t.title, count(1) as replies from vb3_post as p inner join vb3_thread as t using(threadid) where unix_timestamp(now()) - p.dateline < 86400 group by p.threadid order by replies desc limit 5;
+----+-------------+-------+------+---------------+----------+---------+------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | re         | rows  | Extra                           |
+----+-------------+-------+------+---------------+----------+---------+------------+-------+---------------------------------+
|  1 | SIMPLE      | t     | ALL  | PRIMARY       | NULL     | NULL    | NULL       | 39859 | Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref  | threadid      | threadid | 4       | t.threadid |    49 | Using where                     |
+----+-------------+-------+------+---------------+----------+---------+------------+-------+---------------------------------+
2 rows in set (0.01 sec)

mysql> explain select t.threadid, t.title, count(1) as replies from vb3_post as p inner join vb3_thread as t using(threadid) where p.dateline > UNIX_TIMESTAMP(NOW()) - 86400 group by p.threadid order by replies desc limit 5;
+----+-------------+-------+--------+-------------------+----------+---------+------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys     | key      | key_len | ref        | rows | Extra                                        |
+----+-------------+-------+--------+-------------------+----------+---------+------------+------+----------------------------------------------+
|  1 | SIMPLE      | p     | range  | threadid,dateline | dateline | 4       | NULL       | 1171 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY           | PRIMARY  | 4       | p.threadid |    1 |                                              |
+----+-------------+-------+--------+-------------------+----------+---------+------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

So, don’t rest assured that MySQL will use your indexes every time it should. It seems that sometimes you have to explicitly point it out.

Categories
Tips

Reading cookies the regular expression way

While taking a look on the 2nd 24ways article for 2009, I was really surprised to read that “The Web Storage API is basically cookies on steroids, a unhealthy dosage of steroids. Cookies are always a pain to work with. First of all you have the problem of setting, changing and deleting them. Typically solved by Googling and blindly relying on PPK’s solution. (bold is mine)

Of course, there’s nothing wrong with PPK’s solution. It works just fine. However, I always thought his readCookie() function was too verbose and complicated for no reason. It’s a very common example of someone desperately trying to avoid using a regular expression. I googled for “javascript read cookie” and to my surprise, all examples found in the first results were very similar. I never understood why even experienced developers are so scared of regular expressions. Anyway, if anyone wants a shorter function to read a cookie, here’s what I use:

function readCookie(name) {
    // Escape regexp special characters (thanks kangax!)
    name = name.replace(/([.*+?^=!:${}()|[\]\/\\])/g, '\\$1');

    var regex = new RegExp('(?:^|;)\\s?' + name + '=(.*?)(?:;|$)','i'),
        match = document.cookie.match(regex);

    return match && unescape(match[1]); // thanks James!
}

Update: Function updated, see comments below.

I’ve been using it for years and it hasn’t let me down. 🙂

Probably lots of other people have come up and posted something similar before me (I was actually very surprised that something like this isn’t mainstream), but I’m posting it just in case. 🙂

Categories
Original Tips

Yet another email hiding technique?

While exploring browser-supported Unicode characters, I noticed that apart from the usual @ and . (dot), there was another character that resembled an @ sign (0xFF20 or @) and various characters that resembled a period (I think 0x2024 or ․ is closer, but feel free to argue).

I’m wondering, if one could use this as another way of email hiding. It’s almost as easy as the foo [at] bar [dot] com technique, with the advantage of being far less common (I’ve never seen it before, so there’s a high chance that spambot developers haven’t either) and I think that the end result is more easily understood by newbies. To encode foo@bar.com this way, we’d use (in an html page):

foo&#xFF20;bar&#x2024;com

and the result is: foo@bar․com

I used that technique on the ligatweet page. Of course, if many people start using it, I guess spambot developers will notice, so it won’t be a good idea any more. However, for some reason I don’t think it will ever become that mainstream 😛

By the way, if you’re interested in other ways of email hiding, here’s an extensive article on the subject that I came across after a quick googlesearch (to see if somebody else came up with this first — I didn’t find anything).

Categories
Personal Tips

(byte)size matters

Yesterday, I was editing a CSS file and I was wondering how many bytes/KB would a particular addition add to it, in order to decide if it was worth it. Since, I had found myself wondering about the exact same thing multiple times in the past, I decided to make a simple standalone HTML page that would compute the size of any entered text in bytes, KB, MB, etc (whatever was most appropriate). It should be simple and quick and it should account for line terminator differences across operating systems.

About half an hour later, I was done. And then it dawned on me: Someone else might need it too! Since .com domains are, so cheap, hey, let’s get a domain for it as well! There are several sites with a domain that are way simpler than that anyway. A friend that was sitting next to me suggested “sizematters.com” as a joke, but as it turned out, bytesizematters.com was free, so we registered it. And there it is, less than a day after, it’s aliiive. 😛

Any feedback or suggestions are greatly welcome!

For instance, should I implement a very simple minification algorithm and display bytesize for that as well, or is it too much and ruins the simplicity of it without being worth it? [edit: I did it anyway]

Should I implement a way to compare two pieces of text and find out the difference in byte size (could be useful for JavaScript refactoring)? [edit: I did it anyway]

Categories
Original Tips

Bevels in CSS3

Yeah, yeah I know, bevels are soooo 1996. And I agree. However, it’s always good to know the capabilities of your tools. Talented designers will know when it’s suitable to use a certain effect and incapable ones will abuse whatever is given to them, so after a lot of thought, I decided to blog about my discovery.

Categories
Articles Tips

On password masking and usability

I just read Jakob Nielsen’s recent post in which he urged web designers/developers to stop password masking due to it’s inherent usability issues. I found it an interesting read. Hey, at last, someone dared to talk about the elephant in the room!

In most cases password masking is indeed useless, but still, there are several cases where you need that kind of protection. He also points that out, suggesting a checkbox to enable the user to mask their entered password if they wish to do so. He also suggests that checkbox being enabled by default on sites that require high security.

I think the checkbox idea is really good, as long as it works in the opposite way: Password masking should always be the default and you should check the checkbox to show the characters you typed. This is in line with what Windows (Vista or newer) users are already accustomed to anyway

Categories
Articles Tips

Tip: Multi-step form handling

First of all, sorry for my long absence! I haven’t abandoned this blog, I was just really, really busy. I’m still busy, and this probably won’t change soon. However, I will still blog when I get too fed up with work or studying (this is one of these moments…). Now, let’s get to the meat.

The situation

In most web applications, even the simplest ones, the need for form handling will arise. There will be forms that need to be submitted, checked, processed or returned to the user informing them about any errors. A good empirical rule I try to follow is “Try not to produce URLs that don’t have a meaning if accessed directly”. It sounds simple and common-sense, doesn’t it? However, as Francois Voltaire said, “common sense is not so common”. I’ve seen variations of the following scenario several times, in several websites or even commercial web application software:

Categories
Tips

Extend Math.log to allow for bases != e

As Math.log currently stands, it’s a bit useless. It only calculates natural logarithms (base e).  We can easily modify it however, to calculate logarithms of any base:

Math.log = (function() {
	var log = Math.log;
	return function(n, a) {
		return log(n)/(a? log(a) : 1);
	}
})();

We can now specify the base as a second parameter, or still use the default one (Math.E) if we don’t specify one, so older scripts won’t break or if we want a shortcut to the natural logarithm. 😉

Categories
Original Tips

Mockup viewer bookmarklet

I usually view mockups in a browser, so that the impression I get is as close as possible to reality (I learned this the hard way: A mockup that seemed great in the neutral and minimalistic environment of a picture viewer, ended up looking way too fancy when viewed in a browser, something that I realized after having worked for 6 months on the site). If you do the same, I’m sure you’ll feel my pain: Every time I do that, I have to carefully scroll down just as much as to hide the margin that the browser adds, and left just as much as to center the image. Not to mention the click required to enlarge the image to full-size.

Not any more!

Categories
Original Tips

Check whether the browser supports RGBA (and other CSS3 values)

When using CSS, we can just include both declarations, one using rgba, and one without it, as mentioned in my post on cross-browser RGBA backgrounds. When writing JavaScript however, it’s a waste of resources to do that (and requires more verbose code), since we can easily check whether the browser is RGBA-capable, almost as easily as we can check whether it suppports a given property. We can even follow the same technique to detect the support of other CSS3 values (for instance, multiple backgrounds support, HSLA support, etc).