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.

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.

I was doing some research today about how people explain the CSS3 structural* pseudo classes and I stumbled upon this demo by CSS tricks: http://css-tricks.com/examples/nth-child-tester/

I thought the idea is awesome, but lacks a few features:

• It doesn’t use the native browser algorithm for selecting the elements. Granted, it’s not that tough to code your own properly, but I trust a browser implementation more (IE doesn’t support these altogether, so it’s out of the question anyway).
• Doesn’t allow you to test for nth-last-child, nth-of-type, nth-last-of-type (and especially the last two are a lot harder to understand for most people)
• Doesn’t allow you to add/remove list items to see the effects of the selector with different numbers of elements (especially needed if nth-last-child, nth-of-type, nth-last-of-type were involved)

So, I decided to code my own. It allows you to test for all 4 nth-something selectors, supports adding/removing elements (the selected elements update instantly) and uses the native browser implementation to select them (so it won’t work on IE and old browsers).

Enjoy: CSS3 structural pseudo-class selector tester :)

*Yes, :root and :empty also belong to those, but are rarely used. All other structural pseudoclasses are actually shortcuts to some particular case of the aforementioned 4 :)

I was doing some research today about how people explain the CSS3 structural* pseudo classes and I stumbled upon this demo by CSS tricks: http://css-tricks.com/examples/nth-child-tester/

I thought the idea is awesome, but lacks a few features:

• It doesn’t use the native browser algorithm for selecting the elements. Granted, it’s not that tough to code your own properly, but I trust a browser implementation more (IE doesn’t support these altogether, so it’s out of the question anyway).
• Doesn’t allow you to test for nth-last-child, nth-of-type, nth-last-of-type (and especially the last two are a lot harder to understand for most people)
• Doesn’t allow you to add/remove list items to see the effects of the selector with different numbers of elements (especially needed if nth-last-child, nth-of-type, nth-last-of-type were involved)

So, I decided to code my own. It allows you to test for all 4 nth-something selectors, supports adding/removing elements (the selected elements update instantly) and uses the native browser implementation to select them (so it won’t work on IE and old browsers).

Enjoy: CSS3 structural pseudo-class selector tester :)

*Yes, :root and :empty also belong to those, but are rarely used. All other structural pseudoclasses are actually shortcuts to some particular case of the aforementioned 4 :)

I read today CSS Ninja’s (Ryan Sheddon’s) brilliant new technique about the creation of custom checkboxes and radio buttons with CSS alone.

The only thing that bugged me about it was something he pointed himself out as well:

This technique has only 1 drawback I can think of, IE support is not a drawback for me, you can’t use a big sprite image to save all the radio and checkbox states, they need to be individual images. Using CSS generated content to insert an image doesn’t give you control of the image position like a background image does.

And then I wondered “but hey, why can’t we use background images?”. It seemed pretty obvious to me that we could combine a transparent text color with normal css sprites and a display of inline-block in the ::before pseudo-element to achieve the exact same effect. I verified that my initial assertion was actually correct, and tested it in Firefox, Chrome, Safari and Opera (the latest only, no time for testing in older ones at the moment) and it seems to work fine.

Here it is: demo | source files (including .psd file of the sprite)

I’m afraid there’s some blatantly obvious drawback in my approach that made Ryan prefer his method over this, but I’m posting it just in case…