On Tue, Mar 2, 2010 at 10:25 AM, Raman.P <[email protected]> wrote: > > --- 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/ >
Hi Raman, Thanks for explaining. Isn't a join supposed to be always better than IN+subquery? _______________________________________________ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
