I have following partitioned table. I want to get the get the notifications for particular user (using user_id)
CREATE TABLE `notification_activity_partition_user_id` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`activity_id` int(11) NOT NULL,
`activity_type` varchar(32) DEFAULT NULL,
`auther_id` int(11) NOT NULL,
`item_read` tinyint(1) NOT NULL DEFAULT '0',
`timestamp` int(11) DEFAULT '0',
`header_notify_control` int(11) NOT NULL DEFAULT '0',
`email_notify_control` int(11) NOT NULL DEFAULT '0',
`sbcid` int(10) unsigned DEFAULT '0',
PRIMARY KEY (`id`,`user_id`),
KEY `auther_id` (`auther_id`),
KEY `activity_id` (`activity_id`),
KEY `sbcid` (`sbcid`)
) ENGINE=InnoDB AUTO_INCREMENT=13270486 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(user_id)
(PARTITION p0 VALUES LESS THAN (10000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (30000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (40000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (50000) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (60000) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
When i explain this table possible keys is showing auther_id. Why? Why there is no key showing? Is it doign full table scan on partition p0?
explain partitions SELECT count(*) FROM notification_activity_partition_user_id na_outer WHERE na_outer.user_id = 52 AND na_outer.auther_id != 52 AND na_outer.header_notify_control = 1 AND na_outer.item_read = 0 GROUP BY na_outer.activity_id, na_outer.activity_type;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------------------------------------------+
| 1 | SIMPLE | na_outer | p0 | ALL | auther_id | NULL | NULL | NULL | 1079044 | Using where; Using temporary; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------------------------------------------+
1 row in set (0.00 sec)
Please help.
