MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize Inner Join With WHERE BETWEEN dates
Posted by: Anthony Galano
Date: May 24, 2013 12:40PM

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 |
+------------------------------+-------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize Inner Join With WHERE BETWEEN dates
1538
May 24, 2013 12:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.