[Ilugc] mysql query optimization
- From: raamanp@xxxxxxxxxxx (Raman.P)
- Date: Tue Mar 2 10:25:56 2010
--- On Mon, 1/3/10, Deepan Chakravarthy <codeshepherd@xxxxxxxxx> 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/
Other related posts:
