Incoming message have multiple adresses in the envelope-to and to headers. I want to discard all the original recipients and deliver that message to the special single address if some condition matches in ACL. How that can be done?
Two tables exists:
maindata = id, devid, value (10M rows)
djournal = id, devid, md_id_begin, md_id_end, state (10k rows)
I want to select all from maindata for certain devid except rows having wrong state:
SELECT md.*
FROM maindata AS md
LEFT JOIN djournal AS dj
ON md.id BETWEEN dj.md_id_begin AND dj.md_id_end
AND md.devid = dj.devid
WHERE md.devid = 123456789
AND dj.state <> 'idle'
ORDER BY md.id ASC;
Given query produce exactly what I want, but sloooooow. All possible indices has been created.
Sure it's easy to store state field directly in the maindata table, but it's curious why that query is so slow and is any workaround exists?