This query is very slow; how can I optimize it?

[chathura86]

SELECT 
 
MINIMUMPAYMENT.CARDNO,
CARD.NAMEONCARD, 
MINIMUMPAYMENT.MINPAYMENTCOUNT, 
CARD.CREDITLIMIT, 
LASTSTATEMENTSUMMARY.MINAMOUNT AS MIN_PYM, 
CARDTYPE.NAME 
 
FROM 
 
MINIMUMPAYMENT, CARD, CARDTYPE, LASTSTATEMENTSUMMARY
 
WHERE 
 
(CARD.CARDNO = MINIMUMPAYMENT.CARDNO AND CARDTYPE.ID = CARD.CARDTYPEID 
AND LASTSTATEMENTSUMMARY.CARDNO = CARD.CARDNO) AND 
NOT EXISTS(SELECT CRASMF.CARDNO FROM CRASMF WHERE CRASMF.CARDNO = MINIMUMPAYMENT.CARDNO AND CRASMF.STATUS!=2)

Please some one help me to optimize this query because it takes some time to give the resut.the tables are very big, some contains more than 60,000 records.

Regards,

Chathura Bamunusinghe.

[pbmods]

Heya, Chathura.

Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

Please use CODE tags when posting source code:

[CODE=mysql]
MySQL code goes here.
[/CODE]

What do you get when you EXPLAIN your query?

[chathura86]

Heya, Chathura.

Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

Please use CODE tags when posting source code:

MySQL code goes here.
 

What do you get when you EXPLAIN your query?

[HTML]<h3>query result(5 records)</h3><table border=1>
<tr>
<td bgcolor=silver class='medium'>id</td><td bgcolor=silver class='medium'>select_type</td><td bgcolor=silver class='medium'>table</td><td bgcolor=silver class='medium'>type</td><td bgcolor=silver class='medium'>possible_keys</td><td bgcolor=silver class='medium'>key</td><td bgcolor=silver class='medium'>key_len</td><td bgcolor=silver class='medium'>ref</td><td bgcolor=silver class='medium'>rows</td><td bgcolor=silver class='medium'>Extra</td></tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>MINIMUMPAYMENT</td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>1286</td>
<td class='normal' valign='top'>Using where</td>
</tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>LASTSTATEMENTSUMMARY</td>
<td class='normal' valign='top'>eq_ref</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>18</td>
<td class='normal' valign='top'>abccreditcard.MINIMUMPAYMENT.CARDNO</td>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>CARD</td>
<td class='normal' valign='top'>eq_ref</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>18</td>
<td class='normal' valign='top'>abccreditcard.MINIMUMPAYMENT.CARDNO</td>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>CARDTYPE</td>
<td class='normal' valign='top'>eq_ref</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>abccreditcard.CARD.CARDTYPEID</td>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>&nbsp;</td>
</tr>
<tr>
<td class='normal' valign='top'>2</td>
<td class='normal' valign='top'>DEPENDENT SUBQUERY</td>
<td class='normal' valign='top'>CRASMF</td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>1899</td>
<td class='normal' valign='top'>Using where</td>
</tr>
</table>[/HTML]

this is what i got when i export it to html. couldnt find a way to attach it or send it as an image.

regards,

[pbmods]

Heya, Chathura.

Looks like your CRASMF table might not be indexed properly. Be sure that both the CARDNO and STATUS columns are indexed.

Also note that you are returning every single row from MINIMUMPAYMENT. This might be desirable, or it might not. You might want to add a LIMIT clause to your query.

[chathura86]

Heya, Chathura.

Looks like your CRASMF table might not be indexed properly. Be sure that both the CARDNO and STATUS columns are indexed.

Also note that you are returning every single row from MINIMUMPAYMENT. This might be desirable, or it might not. You might want to add a LIMIT clause to your query.

created the indexes but still no different.
cannot put a LIMIT because i want all the rows.

regards,

[chathura86]

created the indexes but still no different.
cannot put a LIMIT because i want all the rows.

regards,

Found the problem. it was caused by a bad relationship

Thanks for the help.

regards.