Krishna Chandra Prajapati schrieb:
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 |
+---------------------------+
as you can see above, is the PRIMARY KEY(`coupon_id`,`course_id`) not only
larger caused by having two fields indexed, also by having more index entries
so it seems not unusual to me that it takes more time to search this index ...
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
