By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,010 Members | 2,774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,010 IT Pros & Developers. It's quick & easy.

How to optimize this slow join?

P: 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
Share this Question
Share on Google+
2 Replies


amitpatel66
Expert 100+
P: 2,367
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
Expert 5K+
P: 5,821
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.