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)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
