Table join is so slow!!

Question

Hi friends, I have joining two tables,There are hundreds of records in table a and there are hundred thousands records in table b.I joined them both inner and left join but it is two slow.My query is:

SELECT 
    ch.id,
    ch.client_client_id,
    ch.duration,
    ch.start,
    ch.isread,
    ch.prefix,
    ucr.ucr_add_factor,
    ucr.ucr_period,
    ucr.ucr_cr_prefix 
FROM 
    call_history AS ch 
    LEFT JOIN tbl_usr_call_rates AS ucr 
        ON (
            ch.prefix=ucr.ucr_cr_prefix 
            AND ch.client_client_id=ucr.ucr_callshop_id
        ) 
WHERE 
    ch.isread='0' 

How can i increase performance

Thanks for advance...

Answer 1

the usual suspects:

check the indexes check that the joins don't require more than one field or you'll have a cartesian product

there are many others but these are the main ones.

jim

Answer 2

I take it you have indexes on these fields?

call_history.prefix
tbl_usr_call_rates.ucr_cr_prefix
call_history.client_client_id
tbl_usr_call_rates.ucr_callshop_id
call_history.isread

Answer 3

You could try by adding an index on the columns your joining on.

Answer 4

Filter first via the WHERE Clause before you join, this Way you join the already filtered Result instead of Joining all of them and then filtering the big Result.

Answer 5

check the used indexes. You can see which are used by call

EXPLAIN SELECT 
ch.id, ch.client_client_id, ch.duration, ch.start, ch.isread, ch.prefix, 
ucr.ucr_add_factor, ucr.ucr_period, ucr.ucr_cr_prefix
FROM call_history AS ch 
LEFT JOIN tbl_usr_call_rates AS ucr
ON (ch.prefix=ucr.ucr_cr_prefix AND ch.client_client_id=ucr.ucr_callshop_id)
WHERE ch.isread='0'