I have the following database structure:
CREATE TABLE IF NOT EXISTS `dailydak` (
`DAK_ID` int(10) NOT NULL auto_increment,
`REC_DATE` date default '0000-00-00',
`DAK_TYPE_ID` int(11) NOT NULL,
`DAIRY_NO` varchar(20) NOT NULL,
`DAK_FROM` varchar(50) default NULL,
`SUBJECT` varchar(255) default '',
`PresidentsRemarks` varchar(255) default NULL,
`ENTRY_U_N` varchar(15) default NULL,
`ENTRY_DATE` date default '0000-00-00',
`DEP_CODE_FROM` varchar(2) default '',
`DEP_CODE_TO` varchar(2) default NULL,
`ACTION_TAKEN_1` varchar(255) default NULL,
`ACTION_TAKEN_DATE_1` date default '0000-00-00',
`ACTION_TAKEN_U_N_1` varchar(15) default NULL,
`ACTION_TAKEN_2` varchar(255) default NULL,
`ACTION_TAKEN_DATE_2` date default '0000-00-00',
`ACTION_TAKEN_U_N_2` varchar(15) default NULL,
PRIMARY KEY (`DAK_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;
-- --------------------------------------------------------
--
-- Table structure for table `dak_types`
--
CREATE TABLE IF NOT EXISTS `dak_types` (
`type_id` int(11) NOT NULL auto_increment,
`dak_type`
http://202.59.74.108/phpmyadmin/index.php?db=lccidak&token=5df78c2b99f20c2d536cd6132cabe3b6 varchar(24) NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='hold the types of dak received' AUTO_INCREMENT=5 ;
-- --------------------------------------------------------
--
-- Table structure for table `department`
--
CREATE TABLE IF NOT EXISTS `department` (
`DEP_CODE` varchar(2) default '',
`DEP_NAME` varchar(40) default NULL,
`PK_ID` int(12) NOT NULL auto_increment,
PRIMARY KEY (`PK_ID`),
UNIQUE KEY `PK_dep_file` (`DEP_CODE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`USERID` int(10) NOT NULL auto_increment,
`U_N` varchar(15) default NULL,
`PASS` varchar(40) NOT NULL,
`ADMIN_STATE` char(1) default '3',
`USER_PERMISSIONS` text,
`DEP_CODE` varchar(2) default NULL,
PRIMARY KEY (`USERID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=76 ;
I need to improve the following query:
SELECT M.rec_date, M.dak_type, M.dairy_no, M.subject, M.dep_name_from, DP.DEP_NAME as
dep_name_to, M.entry_u_n, M.entry_date,
M.action_taken_1, M.action_taken_date_1, M.action_taken_u_n_1,
M.action_taken_2, M.action_taken_date_2, M.action_taken_u_n_2 FROM
(
SELECT d.rec_date, dt.dak_type, d.dairy_no, d.subject, d.dep_code_from,dep.DEP_NAME as dep_name_from,
d.dep_code_to,'' as dep_name_to, d.entry_u_n, d.entry_date,
d.action_taken_1, d.action_taken_date_1, d.action_taken_u_n_1,
d.action_taken_2, d.action_taken_date_2, d.action_taken_u_n_2
FROM dailydak d, department dep, dak_types dt
WHERE (d.dep_code_from=dep.DEP_CODE) and (d.dak_type_id = dt.type_id)
)
M , department DP WHERE M.dep_code_to=DP.DEP_CODE
How can I improve the performance of this query ?
Thank you
Qamar,