Hi,
I am stuck in a very stupid problem and I have tried quite a few things but
it just doesnt work. The problem in short is that one of my queries is
taking TOO LONG in the 'sending state' and for no reason since there are
much more complex queries which don't take more than couple of secs.
Basically its a stored procedure (whole of my application is in stored
procedures and it works fine) and I have written below the relevant queries.
The problematic query is the select count(disc.....) one and I have pasted
its EXPLAIN below as well (after replacing vDocumentTreeID with a value).
c_index_3 is the index on documentcontent.documentid,
documentcontent.documenttreeid.
CREATE TABLE ProspectusList(
ProspectusID BIGINT UNIQUE
);
INSERT INTO ProspectusList (ProspectusID)
SELECT DISTINCT dp.DocumentID
FROM DocumentProperties dp
WHERE dp.FormFamily = '10-K';
SELECT COUNT(DISTINCT dc.DocumentID) INTO vNoOfUnMappedContent
FROM documentcontent dc,ProspectusList pl
where pl.ProspectusID=dc.DocumentID
AND dc.DocumentTreeID=vDocumentTreeID
AND dc.HasChild=0;
"id"; "select_type"; "table"; "type"; "possible_keys";
"key"; "key_len"; "ref";
"rows"; "Extra"
"1"; "SIMPLE"; "pl"; "index"; "ProspectusID";
"ProspectusID"; "9"; NULL;
"7719"; "Using index"
"1"; "SIMPLE" ; "dc"; "ref"; "c_index_3";
"c_index_3"; "9"; "pl.ProspectusID,const";
"1"; "Using where"
Its just examining 7719 rows with an Index and it takes around 20 secs in
sending state. I have also tried removing the INDEX from TEMPORARY table (as
full temp table is being scanned) but its still slow. Its just returning ONE
value so there is no point of sending TOO much data.
Kindly if anyone can shed any light on it I would be grateful. Thanks a lot!
--
Regards,
Salman Akram
