Re: Optimize Inner Join With WHERE BETWEEN dates
Here's the table and the buffer output. Curious why the keylength on table 'a' is four but uses the full key on table 'b'...
CREATE TABLE `records` (
`RecordID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`UserID` int(11) unsigned NOT NULL DEFAULT '0',
`Hours` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
`WorkDescription` text,
`ModificationDescription` varchar(255) DEFAULT NULL,
`AccountCodeID` int(11) unsigned NOT NULL DEFAULT '0',
`ProjectID` int(11) unsigned NOT NULL DEFAULT '0',
`LastChangeByID` int(11) unsigned NOT NULL,
`Paid` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Approved` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Signed` tinyint(3) unsigned NOT NULL DEFAULT '0',
`PayRate` decimal(9,2) unsigned NOT NULL DEFAULT '0.00',
`PaidDate` datetime DEFAULT NULL,
`ClockIn` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ClockOut` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`BreakStart` datetime DEFAULT NULL,
`BreakDuration` tinyint(3) unsigned NOT NULL DEFAULT '0',
`TimezoneID` tinyint(3) unsigned DEFAULT '31',
`DSTEnabled` tinyint(3) unsigned NOT NULL DEFAULT '0',
`LastChangeDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ClockInIPAddress` varchar(15) DEFAULT NULL,
`ClockOutIPAddress` varchar(15) DEFAULT NULL,
`TimeClass` tinyint(3) unsigned NOT NULL DEFAULT '1',
`RecordType` tinyint(3) unsigned NOT NULL DEFAULT '1',
`SystemNote` varchar(200) DEFAULT NULL,
`Flagged` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`RecordID`),
KEY `ix_accrualsum` (`TimeClass`,`Approved`),
KEY `idx_xhours` (`Paid`,`TimeClass`,`UserID`),
KEY `idx_recordalerts` (`UserID`,`Approved`,`Paid`),
KEY `idx_projectid` (`ProjectID`),
KEY `idx_payroll_elligible` (`UserID`,`Paid`,`TimeClass`,`ClockIn`),
KEY `idx_overlappcheck` (`UserID`,`TimeClass`,`RecordID`,`ClockIn`,`ClockOut`),
KEY `idx_hourlyreport` (`UserID`,`AccountCodeID`,`TimeClass`,`Signed`,`Approved`,`Paid`),
KEY `idx_hourlyreport_acct` (`AccountCodeID`,`TimeClass`,`Signed`,`Approved`,`Paid`),
KEY `idx_hourlyreport3` (`UserID`,`TimeClass`,`Paid`),
KEY `fk_records_lastchangebyid` (`LastChangeByID`),
KEY `fk_records_timezoneid` (`TimezoneID`),
CONSTRAINT `fk_records_timezoneid` FOREIGN KEY (`TimezoneID`) REFERENCES `list_timezone` (`TimezoneID`),
CONSTRAINT `fk_records_accountcodeid` FOREIGN KEY (`AccountCodeID`) REFERENCES `accountcode` (`AccountCodeID`),
CONSTRAINT `fk_records_lastchangebyid` FOREIGN KEY (`LastChangeByID`) REFERENCES `users` (`UserID`),
CONSTRAINT `fk_records_projectid` FOREIGN KEY (`ProjectID`) REFERENCES `project` (`ProjectID`),
CONSTRAINT `fk_records_userid` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=8858200 DEFAULT CHARSET=utf8
+------------------------------+-------------+
| Variable_name | Value |
+------------------------------+-------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 34359738368 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 2147483648 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 8388608 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 8388608 |
| sql_buffer_result | OFF |
+------------------------------+-------------+