Re: Why MySQL does not use an index?

[Rick James]

SELECT  COUNT(`id`)
    FROM  `events`
    WHERE  `type` = 'CheckoutEvent'
      AND  `device_ip` = '62.103.172.179'
      AND  created_at > '2012-01-14 07:33:45';

That query needs either of these:
INDEX(type, device_ip, created_at)
INDEX(device_ip, type, created_at)
No other INDEX will perform as well.

Don't use COUNT(id) when COUNT(*) would do. Since id is the PRIMARY KEY, it is necessarily NOT NULL. Hence there is no need to check for NULL, which is what COUNT(id) does.

Note, in particular, INDEX(type, device_ip, created_at) is _not_ the same as
INDEX(type), INDEX(device_ip), INDEX(created_at)

Please use SHOW CREATE TABLE instead of DESCRIBE. The latter does not show the Engine or the charset you are using.

If device_ip is an IP address, I recommend you use VARBINARY(39); that will handle both IPv4 and the new IPv6.