CREATE TABLE `pdf` (
`pdfid` int(11) NOT NULL AUTO_INCREMENT,
`parentid` int(11) NOT NULL,
`caseid` int(11) NOT NULL DEFAULT '0',
`nodid` int(11) NOT NULL DEFAULT '0',
`attorneyid` int(11) NOT NULL DEFAULT '0',
`pdfdiscovered` enum('No','Yes') NOT NULL DEFAULT 'No',
`pdffilename` varchar(64) NOT NULL DEFAULT '',
`pdfdraftedby` tinytext NOT NULL,
`pdfaddressedto` tinytext NOT NULL,
`pdfdatedrafted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pdfdatereceived` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pdfdateloged` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pdfcount` float NOT NULL DEFAULT '0',
`pdfpages` float NOT NULL DEFAULT '0',
`pdfduration` int(5) NOT NULL DEFAULT '0',
`physical_pages` int(11) NOT NULL DEFAULT '-1',
`state` int(3) NOT NULL DEFAULT '1',
`imported` varchar(100) NOT NULL,
PRIMARY KEY (`pdfid`),
KEY `parentid` (`parentid`),
KEY `pdfdatedrafted` (`pdfdatedrafted`),
KEY `caseid` (`caseid`),
KEY `nodid` (`nodid`),
CONSTRAINT `caseid` FOREIGN KEY (`caseid`) REFERENCES `cases` (`caseid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `nodid` FOREIGN KEY (`nodid`) REFERENCES `nod` (`nodid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=92037 DEFAULT CHARSET=latin1
------------------------------------------------------------------------------------------------
This query runs in 2.2s
SELECT `pdf`.`pdfid` AS `pdfid`, `nod`.`nodid` AS `nodid`, `nod`.`nod` AS `nod`, `clients`.`clientid` AS `clientid`, `clients`.`clientname` AS `clientname`, `pdf`.`pdfdraftedby` AS `pdfdraftedby`, `pdf`.`pdfaddressedto` AS `pdfaddressedto`, `pdf`.`pdfdateloged` AS `pdfdateloged`, `pdf`.`attorneyid` AS `attorneyid`, `pdf`.`caseid` AS `caseid`, `pdf`.`pdfdatedrafted` AS `pdfdatedrafted`, `pdf`.`pdfdatereceived` AS `pdfdatereceived`, `pdf`.`pdfdiscovered` AS `discovered`, `pdf`.`pdfpages` AS `legal_pages`, `pdf`.`physical_pages` AS `physical_pages`, count(pdf_child.pdfid) as `children` from pdf left join `nod` on `pdf`.`nodid` = `nod`.`nodid` left join `cases` on `pdf`.`caseid` = `cases`.`caseid` left join `clients` on `cases`.`clientid` = `clients`.`clientid` LEFT JOIN pdf as pdf_child ON pdf_child.parentid = pdf.pdfid WHERE clients.clientid = 235 AND pdf.state = '1' AND pdf.parentID = '0' group by pdf.pdfid ORDER BY `pdfdatedrafted` ASC LIMIT 300, 100;
+----+-------------+-----------+--------+------------------+----------+---------+----------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+------------------+----------+---------+----------------------+------+---------------------------------+
| 1 | SIMPLE | clients | const | PRIMARY | PRIMARY | 4 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | cases | ref | PRIMARY,clientid | clientid | 4 | const | 47 | Using where; Using index |
| 1 | SIMPLE | pdf | ref | parentid,caseid | caseid | 4 | alexmay.cases.caseid | 48 | Using where |
| 1 | SIMPLE | nod | eq_ref | PRIMARY | PRIMARY | 4 | alexmay.pdf.nodid | 1 | |
| 1 | SIMPLE | pdf_child | ref | parentid | parentid | 4 | alexmay.pdf.pdfid | 4763 | Using index |
+----+-------------+-----------+--------+------------------+----------+---------+----------------------+------+---------------------------------+
---------------------------------------------------------------------------------------------------------------
This query runs in 0.4s
SELECT `pdf`.`pdfid` AS `pdfid`, `nod`.`nodid` AS `nodid`, `nod`.`nod` AS `nod`, `clients`.`clientid` AS `clientid`, `clients`.`clientname` AS `clientname`,
// HERE IS THE DIFFERENCE
SUBSTR(pdf.pdfdraftedby,1,100) AS `pdfdraftedby`,
SUBSTR(pdf.pdfaddressedto,1,100) AS `pdfaddressedto`,
//
`pdf`.`pdfdateloged` AS `pdfdateloged`, `pdf`.`attorneyid` AS `attorneyid`, `pdf`.`caseid` AS `caseid`, `pdf`.`pdfdatedrafted` AS `pdfdatedrafted`, `pdf`.`pdfdatereceived` AS `pdfdatereceived`, `pdf`.`pdfdiscovered` AS `discovered`, `pdf`.`pdfpages` AS `legal_pages`, `pdf`.`physical_pages` AS `physical_pages`, count(pdf_child.pdfid) as `children` from pdf left join `nod` on `pdf`.`nodid` = `nod`.`nodid` left join `cases` on `pdf`.`caseid` = `cases`.`caseid` left join `clients` on `cases`.`clientid` = `clients`.`clientid` LEFT JOIN pdf as pdf_child ON pdf_child.parentid = pdf.pdfid WHERE clients.clientid = 235 AND pdf.state = '1' AND pdf.parentID = '0' group by pdf.pdfid ORDER BY `pdfdatedrafted` ASC LIMIT 300, 100;