List:General Discussion« Previous MessageNext Message »
From:Moon's Father Date:June 26 2009 8:26am
Subject:Re: How to Optimize distinct with index
View as plain text  
Hi.
   I think you should create an index like this.
alter table user add index idx_tmp (key1,key2,key3,user_id)

2009/6/19 Darryle Steplight <dsteplight@stripped>

> Select user_id from user where key1=value and
> key2=value2 and key3=value2 GROUP BY user_id
>
>  is faster than
>
>
> Select distinct user_id from user where key1=value and
> key2=value2 and key3=value2;
>
>
> 2009/6/18 周彦伟 <yanwei.zhou@stripped>:
> > Hi,
> >        I have a sql :
> >                Select distinct user_id from user where key1=value and
> > key2=value2 and key3=value2;
> >
> > I add index on (key1,key2,key3,user_id), this sql use temporary table
> > howevery
> > I have thousands of queries per second.
> > How to optimize it?
> >
> >
> > Anthoer question:
> > Select * from user where user_id in(id1,id2,id3,id4,.....) order by
> use_id;
> > I add index on user_id,but after in,order use temporary table, How to
> > optimize it?
> >
> > Thanks!
> >
> > zhouyanwei
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
>
> --
> A: It reverses the normal flow of conversation.
> Q: What's wrong with top-posting?
> A: Top-posting.
> Q: What's the biggest scourge on plain text email discussions?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
David Yeung,
MySQL Senior Support Engineer,
Sun Gold Partner.
My Blog:http://yueliangdao0608.cublog.cn

Thread
Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Matt Neimeyer19 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Johnny Withers19 Jun
    • How to Optimize distinct with index周彦伟19 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Dan Nelson19 Jun
    • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Matt Neimeyer19 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Brent Baisley19 Jun
    • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Matt Neimeyer22 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Peter Brawley20 Jun
    • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Walter Heck - OlinData.com20 Jun
Re: How to Optimize distinct with indexDan Nelson19 Jun
Re: How to Optimize distinct with indexDarryle Steplight19 Jun
  • Re: How to Optimize distinct with indexMoon's Father26 Jun