MySQL: Indexing by column which is also a part of a compound primary keys and I have a table which has a compound primary key, but I would also like to query the table using one or more of the columns composing this primary key. For example:

CREATE TABLE `user_visit_stats` (
  `hour` datetime NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `page_id` int(10) unsigned NOT NULL,
  `visits` int(10) unsigned NOT NULL,
  `avg_time` float unsigned NOT NULL
  PRIMARY KEY  (`hour`,`user_id`,`page_id`)
)


So the primary key is composed of hour, user_id and page_id. I would like to be able to efficiently query the table on different combinations of these columns - for example to get the total visits for a page in a day or the total pages visited by a user in a week. 

Does it make sense to define additional indexes on hour, user_id and page_id or will these indexes be redundant?
visits for a page for a period of time:  INDEX(page_id, hour)

visits for a period of time:  INDEX(hour)

Your primary key, plus these, are not redundant; have them all.

Recommend you keep sum_time, not avg_time, then compute the avg as SUM(sum_time) / SUM(visits).  (The average of averages is not mathematically correct.)