> select pj.populationtype,getPopulationName(pj.populationtype,pj.population) as
populationname,pj.population,pj.yaadid,
> pj.insert_trans_id,pj.invalidate_trans_id,
> j.time,w.login as worker_login,j.action,j.transaction_id as j_transaction_id
> from journal j
> INNER JOIN population2yaad_journal pj on (j.transaction_id=pj.insert_trans_id
> OR j.transaction_id=pj.invalidate_trans_id)
> where j.action = 'add_yaad' or j.action = 'delete_yaad' or j.action = 'change_yaad' order by j.time
Perhaps you can break apart the query by using UNION (ALL). Something
like:
SELECT ...
FROM journal j
JOIN population2yaad_journal pg
ON (j.transaction_id=pj.insert_trans_id)
WHERE j.action = 'add_yaad'
OR j.action = 'delete_yaad'
OR j.action = 'change_yaad'
UNION ALL
SELECT ...
FROM journal j
JOIN population2yaad_journal pg
ON (j.transaction_id=pj.invalidate_trans_id)
WHERE (j.action = 'add_yaad'
OR j.action = 'delete_yaad'
OR j.action = 'change_yaad')
/* Prevent duplicates from UNION *ALL*: */
AND j.transaction_id <> pj.insert_trans_id
> However this does not use the indexes because of the OR in the INNER JOIN.
> How should i create indexes to make this work right?
If this still doesn't work, then perhaps your indexes are not selective
enough or perhaps you need to ANALYZE your table.