--- On Mon, 1/3/10, Deepan Chakravarthy <[email protected]> wrote:
> I am trying to figure out the most > optimal mysql query. > > Query 1: SELECT cid, dl FROM chal > WHERE cid IN ( SELECT cid > FROM c_users WHERE uid = > 636587 ); > Query 2: SELECT chal.cid AS cid, chal.dl > AS dl FROM chal, > c_users WHERE uid = 808 AND > chal.cid = c_users.cid; > > cid is primary key in chal cid and uid are indexed in > c_users, cid is > not unique; > Which of the above query is better? > > Explain says the following > Query 1 uses two types of index namely ALL and > index_subquery > Query 2 users two types of index namely ALL and ref > I wonder why both queries say ALL as type of index though > cid is > primary key in table chal. Based on my experience, observations are a.query analyser decides based on statistics of the data distribution. b.An IN+subquery means in-determinate number as opposed to WHERE. So it may be prudent to do a table scan than index lookup. That may explain why it uses ALL. c.Join on two large tables even on indexed field is not efficient. If such queries can be brought to IN+subquery I feel they will perform better. If chal table is large and c_users small then query 1 should be efficient. If both are large there may not be any difference. Raman.P blog:http://ramanchennai.wordpress.com/ The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ _______________________________________________ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
