Index Not Being Used

[neoform]

Right now I'm completely perplexed as to why I'm getting a table scan instead of the primary index being used (i tried forcing the indexes, it still doesn't use it). It seems so simple it's making me tear out my hair.

 

+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------------+------+-------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                                 | rows | Extra       |
+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------------+------+-------------+
|  1 | SIMPLE      | _clicks       | ALL    | PRIMARY       | NULL    | NULL    | NULL                                |   20 |             |
|  1 | SIMPLE      | _transactions | eq_ref | PRIMARY,id    | PRIMARY | 4       |_clicks.transaction_id               |    1 | Using where |
+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------------+------+-------------+
2 rows in set

 

SELECT 
_transactions.amount, 
_clicks.blah
FROM 	
_transactions
INNER JOIN
_clicks
ON
_transactions.id = _clicks.transaction_id 
WHERE
_transactions.posted_on BETWEEN '2008-09-14 00:00:00' AND '2008-09-24 09:48:00' 

 

CREATE TABLE `_clicks` (
  `transaction_id` int(10) unsigned NOT NULL,
  `blah` char(5) NOT NULL,
  PRIMARY KEY (`transaction_id`)
);

CREATE TABLE `_transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `amount` decimal(10,2) NOT NULL,
  `posted_on` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`posted_on`)
);

INSERT INTO `_clicks` VALUES ('1', 'a');
INSERT INTO `_clicks` VALUES ('2', 'b');
INSERT INTO `_clicks` VALUES ('3', 'c');
INSERT INTO `_clicks` VALUES ('4', 'd');
INSERT INTO `_clicks` VALUES ('5', 'e');
INSERT INTO `_clicks` VALUES ('6', 'f');
INSERT INTO `_clicks` VALUES ('7', 'g');
INSERT INTO `_clicks` VALUES ('8', 'h');
INSERT INTO `_clicks` VALUES ('9', 'i');
INSERT INTO `_clicks` VALUES ('10', 'j');
INSERT INTO `_clicks` VALUES ('11', 'k');
INSERT INTO `_clicks` VALUES ('12', 'l');
INSERT INTO `_clicks` VALUES ('13', 'm');
INSERT INTO `_clicks` VALUES ('14', 'n');
INSERT INTO `_clicks` VALUES ('15', 'o');
INSERT INTO `_clicks` VALUES ('16', 'p');
INSERT INTO `_clicks` VALUES ('17', 'q');
INSERT INTO `_clicks` VALUES ('18', 'r');
INSERT INTO `_clicks` VALUES ('19', 's');
INSERT INTO `_clicks` VALUES ('20', 't');
INSERT INTO `_transactions` VALUES ('1', '24.00', '2008-09-01 10:37:42');
INSERT INTO `_transactions` VALUES ('2', '24.00', '2008-09-02 10:37:46');
INSERT INTO `_transactions` VALUES ('3', '23.00', '2008-09-03 10:37:50');
INSERT INTO `_transactions` VALUES ('4', '636.00', '2008-09-04 10:37:53');
INSERT INTO `_transactions` VALUES ('5', '545.00', '2008-09-05 10:37:55');
INSERT INTO `_transactions` VALUES ('6', '6.00', '2008-09-06 10:37:58');
INSERT INTO `_transactions` VALUES ('7', '46.00', '2008-09-07 10:38:01');
INSERT INTO `_transactions` VALUES ('8', '4.00', '2008-09-08 10:38:04');
INSERT INTO `_transactions` VALUES ('9', '45.01', '2008-09-09 10:38:08');
INSERT INTO `_transactions` VALUES ('10', '5.00', '2008-09-10 10:38:11');
INSERT INTO `_transactions` VALUES ('11', '8.00', '2008-09-11 10:38:14');
INSERT INTO `_transactions` VALUES ('12', '5.12', '2008-09-12 10:38:16');
INSERT INTO `_transactions` VALUES ('13', '7657.00', '2008-09-13 10:38:19');
INSERT INTO `_transactions` VALUES ('14', '79.00', '2008-09-14 10:38:22');
INSERT INTO `_transactions` VALUES ('15', '76.00', '2008-09-15 10:38:24');
INSERT INTO `_transactions` VALUES ('16', '6.00', '2008-09-16 10:38:27');
INSERT INTO `_transactions` VALUES ('17', '3453.00', '2008-09-17 10:38:30');
INSERT INTO `_transactions` VALUES ('18', '445.00', '2008-09-18 10:38:33');
INSERT INTO `_transactions` VALUES ('19', '657.00', '2008-09-19 10:38:35');
INSERT INTO `_transactions` VALUES ('20', '64.00', '2008-09-20 10:38:38');

 

 

[fenway]

It seems to be joining the tables the wrong way... use straight join, or try running analyze.