469,091 Members | 1,154 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

How to optimize this slow join?

43
hi everyone , i am trying to join two tables , the query is working fine when the records in the tables are less but when the records are in huge numbers say 1-2 million then the system hangs... i am using version 4.1 and the query is

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(dummy.username)AS count
  2. FROM dummy2 
  3. LEFT JOIN dummy ON dummy2.msg_id = dummy.msg_id
  4. where dummy2.username like 'xyz' 
  5. GROUP BY dummy2.username
plz give any suggestions to optimize this so that it works for large records also..
Oct 26 '07 #1
2 1856
amitpatel66
2,367 Expert 2GB
hi everyone , i am trying to join two tables , the query is working fine when the records in the tables are less but when the records are in huge numbers say 1-2 million then the system hangs... i am using version 4.1 and the query is

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(dummy.username)AS count
  2. FROM dummy2 
  3. LEFT JOIN dummy ON dummy2.msg_id = dummy.msg_id
  4. where dummy2.username like 'xyz' 
  5. GROUP BY dummy2.username
plz give any suggestions to optimize this so that it works for large records also..
Use an = operator insted of LIKE.

Eg:

dummy2.username = 'xyz'

This will improve the performance for sure
Oct 26 '07 #2
pbmods
5,821 Expert 4TB
Heya, Bravo.

Changed thread title to better describe the problem (threads whose titles do not follow the Posting Guidelines actually get fewer responses).

What happens when you EXPLAIN the query?
Oct 26 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by mjuricek | last post: by
12 posts views Thread by Neil | last post: by
2 posts views Thread by Yonatan Goraly | last post: by
reply views Thread by Michal Hlavac | last post: by
4 posts views Thread by Huaer.XC | last post: by
19 posts views Thread by octangle | last post: by
2 posts views Thread by bsagert | last post: by
2 posts views Thread by existential.philosophy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.