Query optimization needed

[Devrishi Shandilya]

Hi All,

I have a query which taking 4-5 seconds to complete.

select distinct
(f.id) as id,
f.access_matrix_type as acessmatrixtype,
f.folder_name as foldername,
f.approval_folder_type as approvalfoldertype,
f.parent_folder_id as parentfolderid,
check_cpconfo_records(ff.id ) as countsubfolders,
(select
count(dp1.id)
from document_permission dp1
where dp1.permission_type = 2
and dp1.user_id = '12'
and dp1.status = 1
and dp1.document_id in(select
mf1.id
from master_file mf1
where mf1.folder_id = f.id
and mf1.status in(1, 2, 6))) as docwriper,
(select
count(mf2.id)
from master_file mf2
where mf2.folder_id = f.id
and mf2.status in(1, 2, 6)) as hasactivefile,
f.type_id as dealid,
f.dynamic_folder as dynamicFolder,
f.created_on as createdOn,
f.cancel as cancel,
ff.created_on as parentFolderCreation,
ff.dynamic_folder as parentFolderDynamic,
ff.cancel as parentFolderCancel,
ff.notice_type as parentFolderNotice,
ff.folder_name as parentFolderName,
f.notice_type as noticeType,
f.folder_process as folderProcess
from folders f
left join folders ff
on ff.id = f.parent_folder_id
join master_file mf
on f.id = mf.folder_id
join document_permission dp
on mf.id = dp.document_id
where dp.user_id = '12'
and dp.permission_type in(1,2)
and f.type_id in(12,11,10,9,8,7,6,5,4,3,2);

----------------------------------------------------------------------

I have done/check following things.

1- Check for index fields, it seems OK. explain plan is attached.
2- Create this part of query as function, which reduce the time to 2-3 seconds.

select
count(dp1.id)
from document_permission dp1
where dp1.permission_type = 2
and dp1.user_id = '12'
and dp1.status = 1
and dp1.document_id in(select
mf1.id
from master_file mf1
where mf1.folder_id = f.id
and mf1.status in(1, 2, 6))) as docwriper

3- Now i need to optimize it further. can it possiable. please suggest.


id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY dp ref Index_DocumentId,Index_UserId Index_UserId 5 const 398 Using where; Using temporary

1 PRIMARY mf eq_ref PRIMARY,Index_folder_id PRIMARY 4 scagency19.dp.document_id 1 Using where

1 PRIMARY f eq_ref PRIMARY,Index_type_idPRIMARY 4 scagency19.mf.folder_id1 Using where

1PRIMARYffeq_refPRIMARYPRIMARY4scagency19.f.parent_folder_id1(NULL)

4DEPENDENT SUBQUERYmf2refIndex_folder_idIndex_folder_id5scagency19.f.id1Using where

2DEPENDENT SUBQUERYmf1refPRIMARY,Index_folder_idIndex_folder_id5scagency19.f.id1Using where

2DEPENDENT SUBQUERYdp1refIndex_DocumentId,Index_UserIdIndex_DocumentId5scagency19.mf1.id13Using where

-----------------------------------------------------------------------------

Table Structures :

CREATE TABLE `document_permission` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`document_id` int(10) unsigned DEFAULT NULL,
`permission_type` tinyint(3) unsigned DEFAULT NULL,
`created_on` timestamp NULL DEFAULT '0000-00-00 00:00:00',
`created_by` int(10) unsigned DEFAULT NULL,
`modified_on` timestamp NULL DEFAULT NULL,
`modified_by` int(10) unsigned DEFAULT NULL,
`if_requested` int(10) unsigned DEFAULT NULL,
`request_date` datetime DEFAULT NULL,
`allowed_on_date` datetime DEFAULT NULL,
`status` tinyint(3) unsigned DEFAULT NULL,
`group_id` int(10) unsigned DEFAULT NULL,
`user_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Index_DocumentId` (`document_id`),
KEY `Index_UserId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

----------------------------------------------------------------------

CREATE TABLE `master_file` (
`id` int(6) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Its Generated a pirimary key',
`name` varchar(320) DEFAULT NULL COMMENT 'Name of uploaded file(also show version of file name.',
`folder_id` int(6) unsigned DEFAULT NULL COMMENT 'Refrance the id of folder table.',
`status` tinyint(2) unsigned DEFAULT NULL COMMENT '1=Active, 0= Delete file from frontend, 2= checkout the document, 3= checkin the document but currently not in use.(after the status=2 if we checkin the document then the status become 3 and the file use only show in version history). status=8 (NOTICE AP',
`active` varchar(202) DEFAULT NULL COMMENT '1= file is active',
`uploaded_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Generated the current time stamp',
`actual_name` varchar(450) DEFAULT NULL COMMENT 'timestamp+original name of the document(where we have capture the document)',
`type_id` int(6) unsigned DEFAULT NULL COMMENT 'Show the Deal Id',
`modified_on` timestamp NULL DEFAULT NULL COMMENT 'Generated the current time stamp',
`uploaded_by` int(6) unsigned DEFAULT NULL COMMENT 'ID of the user who upload the document',
`source` varchar(230) DEFAULT NULL COMMENT 'Show the source destination whatever we written on uploading a file ',
`comment` varchar(2000) DEFAULT NULL COMMENT 'Show the Comments whatever we written on uploading a file ',
`group_no` int(6) unsigned DEFAULT NULL COMMENT 'group_id is generated and its reflects that to show the version history.',
`file_size` int(10) unsigned DEFAULT NULL COMMENT 'Discribe that file size on disk',
`owner_id` int(6) unsigned DEFAULT NULL COMMENT 'Show the user "id", and its shows that which user is created the file/folder.',
`if_approval_required` tinyint(2) unsigned DEFAULT '0' COMMENT 'Null value generated.',
`valid_from` datetime DEFAULT NULL COMMENT 'Last date of valid file reply',
`request_confirmation_id` int(6) unsigned DEFAULT NULL COMMENT 'request id ',
`rollover_id` int(6) unsigned DEFAULT NULL,
`user_type` int(6) DEFAULT NULL,
`file_type` tinyint(2) unsigned DEFAULT NULL COMMENT '1=Public, 2=Private',
`request_reference_no` int(10) unsigned DEFAULT NULL,
`request_subject` varchar(250) DEFAULT NULL,
`notice_type` varchar(250) DEFAULT NULL COMMENT '-1=DD-Ro (Parent),1=DD-RO value date,2=B,3=L,-2= Rate Fixing,4= Rate Fixing Notices,5= B,6= L ,7= Rate Changes notification,',
`notice_id` int(10) unsigned DEFAULT NULL,
`if_confirm` int(6) DEFAULT NULL COMMENT '0=Not Require , 1= Required/Pending , 2= Confirmed , 3= Rejected',
`enquiry_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Index_folder_id` (`folder_id`),
KEY `Index_owner_id` (`owner_id`),
KEY `Index_type_id` (`type_id`),
KEY `Index_NoticeId` (`notice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=854 DEFAULT CHARSET=latin1


-------------------------------------------------------------------------------

CREATE TABLE `folders` (
`id` int(6) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Its Generated a pirimary key',
`type` varchar(230) DEFAULT NULL COMMENT 'Weather its (deal specific, facility specific,loan spectfic, others)',
`folder_name` varchar(1100) DEFAULT NULL COMMENT 'Name of Folder/Subfolder',
`type_id` int(6) unsigned DEFAULT NULL COMMENT 'Show the Deal Id',
`status` tinyint(2) unsigned DEFAULT NULL COMMENT '1=Active, 0= Delete file from frontend',
`access_matrix_type` tinyint(2) unsigned DEFAULT NULL COMMENT '1= File based , 2= Folder Based',
`created_by` int(6) unsigned DEFAULT NULL COMMENT 'Show the user id who created file/folder.',
`created_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Generated the current time stamp',
`modified_by` int(6) unsigned DEFAULT NULL COMMENT 'Default Null value generated if another user modified then its pump the user id on them',
`modified_on` timestamp NULL DEFAULT NULL COMMENT 'Generated the current time stamp',
`parent_folder_id` int(6) unsigned NOT NULL COMMENT '0=default , otherwise Shows master_id of subfolder.',
`approval_folder_type` int(11) DEFAULT NULL COMMENT 'null= default, -1= only show the details in dropdown.',
`actual_folder_name` varchar(1100) DEFAULT NULL COMMENT 'Timestamp+folder_name',
`display_folder_name` varchar(1100) DEFAULT NULL,
`grand_folder_id` int(11) DEFAULT NULL,
`notice_type` int(11) DEFAULT NULL,
`dynamic_folder` tinyint(1) DEFAULT NULL,
`cancel` tinyint(1) unsigned DEFAULT NULL,
`folder_process` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Index_created_by` (`created_by`),
KEY `Index_type_id` (`type_id`),
KEY `FK_folders` (`parent_folder_id`)
) ENGINE=InnoDB AUTO_INCREMENT=571 DEFAULT CHARSET=latin1