Re: [Ilugc] mysql query optimization
On Tue, Mar 2, 2010 at 12:33 PM, praveen kumar wrote: >> >> >> Thanks for explaining. Isn't a join supposed to be always better than >> IN+subquery? >> >> > > if the inner query results are less then sub query will outperform the join > query (especially when using IN) > Thanks praveen ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
[Ilugc] mysql query optimization
> > > Thanks for explaining. Isn't a join supposed to be always better than > IN+subquery? > > if the inner query results are less then sub query will outperform the join query (especially when using IN) -- Praveen kumar p +91 9884410904 ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
Re: [Ilugc] mysql query optimization
On Tue, Mar 2, 2010 at 10:25 AM, Raman.P wrote: > > --- On Mon, 1/3/10, Deepan Chakravarthy 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
Re: [Ilugc] mysql query optimization
--- On Mon, 1/3/10, Deepan Chakravarthy 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
[Ilugc] mysql query optimization
I am trying to figure out the most optimal mysql query. Query 1: SELECT cid, dl FROMchal WHERE cid IN ( SELECT cid FROMc_users WHERE uid = 636587 ); Query 2: SELECT chal.cid AS cid, chal.dl AS dl FROMchal, c_users WHERE uid = 808 ANDchal.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. I have posted the same at http://stackoverflow.com/questions/2356714/mysql-query-optimisation Regards Deepan +91 9945702482 http://www.hashcube.com http://twitter.com/codeshepherd ___ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
