Some observations and things to try (these examples are from a famous mysql test database called sakila);
CREATE TABLE `actor` (
`actor_id` smallint(5) unsigned NOT NULL auto_increment,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> show index from actor;
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| actor | 0 | PRIMARY | 1 | actor_id | A | 200 | NULL | NULL | | BTREE | |
| actor | 1 | idx_actor_last_name | 1 | last_name | A | 200 | NULL | NULL | | BTREE | |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
mysql> explain select first_name from actor where actor_id < 11 and last_update < '2006-02-15 04:34:34';
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | actor | range | PRIMARY | PRIMARY | 2 | NULL | 10 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
These results look more like what I would expect than yours do.
Actually, just looking at this it looks to me like last_check_date is a multiple-column index using sent_date, offline_count and status, there's also one composed of id and status so I'd say that as explain was trying to tell us there was no suitable index for the query.
Try this;
create index stat on userids (status); // may use, may improve speed
create index sent on userids (sent_date); // may use, may improve speed, may use in conjunction with stat?
create index sent_stat on userids (status,sent_date); // Order important?
create index stat_sent on userids (sent_date,status); // Looks like the winner, test them all though
Now you may have to try each one uniquely (not sure here), so create one and delete the others then test with explain to see whether it's using an index and what your speed is like. If you throw all the indexes in there together it may be smart enough to pick the best one (prolly is) but it may just muddy the waters so I'd do each in isolation and then all together to see if it uses more than one etc.
Given that we are creating these indexes explicitly on the columns the queries are based on they should help a lot.
Oh and use analyze (help analyze table) and explain a lot and maybe consider optimize (help optimize table) if you suspect you have "holes" in your data.
Report back dude.
Cheers,
td
