SEO

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

On Thu, Apr 24, 2008 at 6:40 AM, Krishna Chandra Prajapati
<prajapatikc@gmail.com> 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
wultsch@gmail.com
wultsch (aim)

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 <wultsch@gmail.com> wrote:

> On Thu, Apr 24, 2008 at 6:40 AM, Krishna Chandra Prajapati
> <prajapatikc@gmail.com> 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
> wultsch@gmail.com
> wultsch (aim)
>



--
Krishna Chandra Prajapati

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