I have what I think might be a tough challenge. In essence I have 3 very large tables:
email.dirs.notesid = extraction_file_detail.not
ocnumber = extract_document_list.f_do
I have data in email.dirs that I need to map to data in extract_document_list.
Here are a few attempts:
EXPLAIN select * from p2.extract_document_list where f_docnumber in
from p2.extraction_file_detail e
inner join email.dirs d on d.notesid = e.notesid
and d.flag = 3);
EXPLAIN select * from p2.extract_document_list
where f_docnumber in (select f_docnumber from p2.extraction_file_detail e
where exists (select * from email.dirs d
where d.notesid = e.notesid
and d.flag = 3));
And a few others of simliar types, all of which result in extremely slow response.
I can tell you that, with exception of flag, all of the fields I am searching on are primary keys. This is why I thought the searches would be faster.
So my questions are, is there a better way to search? I have tried JOIN, EXISTS, IN, all 3 I never got to actually finish before giving up and stopping the query. Or, do I need to think about how the database is optimized?
Any assistance would be very much appreciated.