How to optimize this slow join?

[bravo]

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

SELECT COUNT(dummy.username)AS count
FROM dummy2 
LEFT JOIN dummy ON dummy2.msg_id = dummy.msg_id
where dummy2.username like 'xyz' 
GROUP BY dummy2.username

plz give any suggestions to optimize this so that it works for large records also..

[amitpatel66]

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

SELECT COUNT(dummy.username)AS count
FROM dummy2 
LEFT JOIN dummy ON dummy2.msg_id = dummy.msg_id
where dummy2.username like 'xyz' 
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

[pbmods]

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?