Hi wultsch, Thanks a lot. Every thing is going fine. I am only concerned with duplicate index, as it is using disk space.
Is there any solution so that i can ignore duplicate index by altering the table design. OR i have to end up with duplicate index. Thanks, Krishna Chandra Prajapati On Thu, Apr 24, 2008 at 9:27 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote: > On Thu, Apr 24, 2008 at 6:40 AM, Krishna Chandra Prajapati > <[EMAIL PROTECTED]> wrote: > > 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 > > > Hi Krishna, > I have run into similar issues in the past and have ended up having > duplicative indexes. The multi column indexes have higher cardinality > and although it should not be an issue, lookup on the first portion of > the index alone is not as efficient. I would love to know why this > is/what I am dong wrong. > > Are you having issues with INSERT speed, or the size of the your indexes? > > Posting your explain (extended) and show index may be helpful. > > For whatever it is worth, I always suggest explicit joins and using AS: > SELECT sql_no_cache ac.plan > FROM coupon_per_course AS cpc > INNER JOIN affiliate_coupon AS ac USING(coupon_id) > WHERE cpc.course_id = 213336 > AND ac.coupon_code='TST0G0' > > I think it makes queries much easier to read and understand. > > -- > Rob Wultsch > [EMAIL PROTECTED] > wultsch (aim) > -- Krishna Chandra Prajapati
