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;