gistfile1.txt

# mysql 5.5.10 homebrew, Macbook Air 1.6GHz Core2Duo
# innodb_buffer_pool_size = 32MB

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.10 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

mysql> explain select count(*) from entries where created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59' and is_mobile=1;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | entries | ALL  | NULL          | NULL | NULL    | NULL | 98326 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from entries where created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59' and is_mobile=1;
+----------+
| count(*) |
+----------+
|      315 |
+----------+
1 row in set (1.03 sec)

mysql> explain select count(*) from entries force index(user_id) where created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59' and is_mobile=1;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | entries | ALL  | NULL          | NULL | NULL    | NULL | 98326 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from entries force index(user_id) where created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59' and is_mobile=1;
+----------+
| count(*) |
+----------+
|      315 |
+----------+
1 row in set (1.04 sec)

mysql> explain select count(*) from (select is_mobile from entries where created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59') e where is_mobile=1;
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  3187 | Using where |
|  2 | DERIVED     | entries    | ALL  | NULL          | NULL | NULL    | NULL | 98326 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
2 rows in set (1.03 sec)

mysql> select count(*) from (select is_mobile from entries where created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59') e where is_mobile=1;
+----------+
| count(*) |
+----------+
|      315 |
+----------+
1 row in set (1.04 sec)


mysql> explain SELECT COUNT(*) FROM entries a, entries b  WHERE a.id=b.id   AND a.created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'   AND b.is_mobile=1;
+----+-------------+-------+--------+---------------+---------+---------+-----------+-------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows  | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+-----------+-------+--------------------------+
|  1 | SIMPLE      | a     | index  | PRIMARY       | user_id | 13      | NULL      | 98326 | Using where; Using index |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.id |     1 | Using where              |
+----+-------------+-------+--------+---------------+---------+---------+-----------+-------+--------------------------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM entries a, entries b  WHERE a.id=b.id   AND a.created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'   AND b.is_mobile=1;
+----------+
| COUNT(*) |
+----------+
|      315 |
+----------+
1 row in set (0.51 sec)

test.pl

#!/usr/bin/env perl

use strict;
use warnings;
use DBIx::Sunny;
use Digest::SHA qw/sha1_hex/;

my $dbh = DBIx::Sunny->connect('dbi:mysql:test');
$dbh->query(q{DROP TABLE IF EXISTS entries});
$dbh->query(q{CREATE TABLE entries (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    is_mobile TINYINT UNSIGNED NOT NULL,
    title VARCHAR(512) NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    status TINYINT UNSIGNED NOT NULL,
    INDEX (user_id,status,created_at)
) ENGINE=InnoDB});

sub genbody {
    my $body='';
    my $length = 30 + int(rand(10));
    for (1..$length) {
        $body .= sha1_hex(rand(0xff));
    }
    $body;
}

for ( 1..100000 ) {
    my @lt = localtime( time + 60*int(rand(24*60)) + 86400*int(rand(30)) );
    $dbh->query(
        q{INSERT INTO entries (user_id, is_mobile, title, body, created_at,status) 
          VALUES (?,?,?,?,?,?)},
        int(rand(0xff)+1),
        ( int(rand(10)) == 0 ) ? 1 : 0,
        sha1_hex(rand(0xff)) . sha1_hex(rand(0xff)),
        genbody(),
        sprintf("%04d-%02d-%02d %02d:%02d:%02d",$lt[5]+1900,$lt[4]+1,$lt[3],$lt[2],$lt[1],$lt[0]),
        1,
    ); 
}