Table Design

[Krishna Chandra Prajapati]

Hi All,

Below is the table design on mysql server.

CREATE TABLE `coupon_per_course` (
  `coupon_id` int(10) unsigned NOT NULL default '0',
  `course_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`coupon_id`,`course_id`),
  KEY `idx_coupon_per_course` (`coupon_id`),
  KEY `idx_coupon_per_course_1` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

In my view index idx_coupon_per_course should not be there. Since coupon_id
is a primary key. so it will be utilized for searching.

Before removing index idx_coupon_per_course
mysql> do benchmark(1000000,(select sql_no_cache ac.plan from
affiliate_coupon ac, coupon_per_course cpc  where ac.coupon_code='TST0G0'
and ac.coupon_id = cpc.coupon_id and  cpc.course_id = 213336));
Query OK, 0 rows affected (0.06 sec)


After removing index idx_coupon_per_course
mysql> do benchmark(1000000,(select sql_no_cache ac.plan from
affiliate_coupon ac, coupon_per_course cpc  where ac.coupon_code='TST0G0'
and ac.coupon_id = cpc.coupon_id and  cpc.course_id = 213336));
Query OK, 0 rows affected (0.07 sec)

I am not able to understand why after removing the index
idx_coupon_per_course, it is taking more time. As it must take less time.

Some other statistics are
mysql> select count(*) from coupon_per_course;
+----------+
| count(*) |
+----------+
|   296218 |
+----------+
mysql> select count(distinct coupon_id) from coupon_per_course;
+---------------------------+
| count(distinct coupon_id) |
+---------------------------+
|                    211519 |
+---------------------------+

Please suggest me the correct table design.
Thanks in advance.

Thanks,
-- 
Krishna Chandra Prajapati