Re: Query Takes Longer Each Time Its Run

[anthony]

HERE IS THE QUERY:

( SELECT
users.FirstName,
users.LastName,
users.JobTitle,
users.EmployeeNumber,
records.RecordID,
records.UserID,
records.ClockIn,
records.BreakStart,
records.ClockOut,
IF(records_splitpaid.RecordID IS NULL,
ConvertGMTDateToLocalDate(records.ClockIn,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset),
ConvertGMTDateToLocalDate(records_splitpaid.ClockIn,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset)
) AS ClockInLocal,
IF(records.BreakStart IS NULL,NULL,ConvertGMTDateToLocalDate(records.BreakStart,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset)) AS BreakStartLocal,
IF(records_splitpaid.RecordID IS NULL,
ConvertGMTDateToLocalDate(records.ClockOut,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset),
ConvertGMTDateToLocalDate(records_splitpaid.ClockOut,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset)
) AS ClockOutLocal,
IF(records.DSTEnabled = 1,timezone_dst_abbr,timezone_nondst_abbr) AS TimezoneLocal,
IF(records_splitpaid.RecordID IS NULL,records.Hours,records_splitpaid.Hours) AS Hours,
records.BreakDuration,
records.AccountCodeID,
records.ProjectID,
records.Signed,
records.Approved,
records.Paid,
records.PayRate,
records.TimeClass,
(records.PayRate*records.Hours) AS PayTotal,
accountcode.AccountCodeName,
IFNULL ( label.LabelName,
( CASE records.TimeClass
WHEN 1 THEN 'Standard'
WHEN 2 THEN 'Sick'
WHEN 3 THEN 'Vacation'
WHEN 4 THEN 'PTO'
WHEN 5 THEN 'Holiday'
WHEN 6 THEN 'Other1'
WHEN 8 THEN 'Other2'
WHEN 7 THEN 'Salaried'
END ) ) AS TimeClassLabel
FROM records
LEFT JOIN records_splitpaid ON records.RecordID = records_splitpaid.RecordID
JOIN users ON records.UserID = users.User_ID
JOIN accountcode ON records.AccountCodeID = accountcode.AccountCodeID
JOIN tco_listdata.list_timezone ON records.TimezoneID = tco_listdata.list_timezone.Timezone_ID
LEFT JOIN label ON users.CompanyID = label.CompanyID AND records.TimeClass = label.TimeClass
WHERE IF( FIND_IN_SET('0','0,1') != 0, 1=1,0=1 )
AND records.UserID = 23993
AND records.AccountCodeID IN (4098,1794,1795,1796,1797,1798,1799,1800,1801)
AND records.TimeClass IN (1,2,3,4,5,6,8,7)
AND records.Signed IN (0,1)
AND records.Approved IN (2,0,1)
AND records.Paid IN (0,2)
AND IF(records_splitpaid.RecordID IS NULL,
ConvertGMTDateToLocalDate(records.ClockIn,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset),
ConvertGMTDateToLocalDate(records_splitpaid.ClockIn,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset)
) BETWEEN '2011-01-01 00:00:00' AND '2011-01-25 23:59:59'
) UNION (
SELECT
users.FirstName,
users.LastName,
users.JobTitle,
users.EmployeeNumber,
records.RecordID,
records.UserID,
records.ClockIn,
records.BreakStart,
records.ClockOut,
ConvertGMTDateToLocalDate(records.ClockIn,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset) AS ClockInLocal,
IF(records.BreakStart IS NULL,NULL,ConvertGMTDateToLocalDate(records.BreakStart,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset)) AS BreakStartLocal,
IF(records_splitpaid.RecordID IS NULL,
ConvertGMTDateToLocalDate(records.ClockOut,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset),
ConvertGMTDateToLocalDate(records_splitpaid.ClockIn,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset)
) AS ClockOutLocal,
IF(records.DSTEnabled = 1,timezone_dst_abbr,timezone_nondst_abbr) AS TimezoneLocal,
IF(records_splitpaid.RecordID IS NULL,records.Hours,ABS(records.Hours-records_splitpaid.Hours)) AS Hours,
records.BreakDuration,
records.AccountCodeID,
records.ProjectID,
records.Signed,
records.Approved,
records.Paid,
records.PayRate,
records.TimeClass,
(records.PayRate*records.Hours) AS PayTotal,
accountcode.AccountCodeName,
IFNULL ( label.LabelName,
( CASE records.TimeClass
WHEN 1 THEN 'Standard'
WHEN 2 THEN 'Sick'
WHEN 3 THEN 'Vacation'
WHEN 4 THEN 'PTO'
WHEN 5 THEN 'Holiday'
WHEN 6 THEN 'Other1'
WHEN 8 THEN 'Other2'
WHEN 7 THEN 'Salaried'
END ) ) AS TimeClassLabel
FROM records
LEFT JOIN records_splitpaid ON records.RecordID = records_splitpaid.RecordID
JOIN users ON records.UserID = users.User_ID
JOIN accountcode ON records.AccountCodeID = accountcode.AccountCodeID
JOIN tco_listdata.list_timezone ON records.TimezoneID = tco_listdata.list_timezone.Timezone_ID
LEFT JOIN label ON users.CompanyID = label.CompanyID AND records.TimeClass = label.TimeClass
WHERE IF( FIND_IN_SET('1','0,1') != 0, 1=1,0=1 )
AND records.UserID = 23993
AND records.AccountCodeID IN (4098,1794,1795,1796,1797,1798,1799,1800,1801)
AND records.TimeClass IN (1,2,3,4,5,6,8,7)
AND records.Signed IN (0,1)
AND records.Approved IN (2,0,1)
AND records.Paid IN (1,2)
AND ConvertGMTDateToLocalDate(records.ClockIn,timezone_nondst_gmt_offset,records.DSTEnabled,timezone_dst_gmt_offset)
BETWEEN '2011-01-01 00:00:00' AND '2011-01-25 23:59:59'
) ORDER BY ClockInLocal


HERE IS THE EXPLAIN:

+----+--------------+-------------------+--------+--------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------------+--------+--------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------+------+----------------+
| 1 | PRIMARY | users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 1 | PRIMARY | records | range | ix_unpaidhours,ix_accrualsum,ix_userid,idx_xhours,idx_accountcodeid,idx_recordalerts,idx_usertimeclass | ix_unpaidhours | 5 | NULL | 3 | Using where |
| 1 | PRIMARY | accountcode | eq_ref | PRIMARY,idx_accountcodeid | PRIMARY | 4 | records.AccountCodeID | 1 | |
| 1 | PRIMARY | list_timezone | eq_ref | PRIMARY | PRIMARY | 1 | records.TimezoneID | 1 | |
| 1 | PRIMARY | records_splitpaid | eq_ref | PRIMARY | PRIMARY | 4 | records.RecordID | 1 | Using where |
| 1 | PRIMARY | label | ref | idx_timeclasscompany | idx_timeclasscompany | 5 | const,records.TimeClass | 1 | |
| 2 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | UNION | records | ref | ix_unpaidhours,ix_accrualsum,ix_userid,idx_xhours,idx_accountcodeid,idx_recordalerts,idx_usertimeclass | ix_userid | 4 | const | 1526 | Using where |
| 2 | UNION | list_timezone | eq_ref | PRIMARY | PRIMARY | 1 | records.TimezoneID | 1 | Using where |
| 2 | UNION | accountcode | eq_ref | PRIMARY,idx_accountcodeid | PRIMARY | 4 | records.AccountCodeID | 1 | |
| 2 | UNION | records_splitpaid | eq_ref | PRIMARY | PRIMARY | 4 | records.RecordID | 1 | |
| 2 | UNION | label | ref | idx_timeclasscompany | idx_timeclasscompany | 5 | const,records.TimeClass | 1 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+----+--------------+-------------------+--------+--------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------+------+----------------+

HERE ARE THE TABLES:

--
-- Table structure for table users
--

CREATE TABLE `users` (
`user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`UserStatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
`CompanyID` int(11) unsigned NOT NULL DEFAULT '0',
`Username` varchar(60) NOT NULL DEFAULT '',
`Password` varchar(64) DEFAULT NULL,
`NextPasswordReset` datetime DEFAULT NULL,
`access` enum('EMP','SUPER','ADMIN') NOT NULL DEFAULT 'EMP',
`PermissionFlags` int(11) unsigned NOT NULL DEFAULT '0',
`ApplicationFlags` int(11) unsigned NOT NULL DEFAULT '0',
`FirstName` varchar(35) DEFAULT NULL,
`LastName` varchar(35) DEFAULT NULL,
`EmployeeType` enum('EMPLOYEE','CONTRACTOR') NOT NULL DEFAULT 'EMPLOYEE',
`PayType` enum('HOURLY','SALARY') NOT NULL DEFAULT 'HOURLY',
`JobTitle` varchar(100) NOT NULL,
`dateCreated` datetime DEFAULT NULL,
`DisplayPreference` enum('HOURLY','PROJECT','EXPENSE','ANNOUNCEMENT','HR') NOT NULL DEFAULT 'HOURLY',
`timezone_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`timezone_enable_dst` tinyint(3) unsigned NOT NULL DEFAULT '0',
`AdminUserID` int(11) NOT NULL DEFAULT '0',
`EmployeeNumber` varchar(40) NOT NULL,
`Rate` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
`SalaryRate` decimal(9,2) NOT NULL DEFAULT '0.00',
`Phone` varchar(25) NOT NULL DEFAULT '',
`Location` varchar(50) DEFAULT NULL,
`Division` varchar(50) DEFAULT NULL,
`Department` varchar(50) DEFAULT NULL,
`DateOfHire` date DEFAULT NULL,
`DateOfTermination` datetime DEFAULT NULL,
`AnnualReviewDate` date DEFAULT NULL,
`DateOfBirth` date DEFAULT NULL,
`IPLockExempt` tinyint(3) unsigned NOT NULL DEFAULT '0',
`ClockActionPhotoExempt` tinyint(3) unsigned NOT NULL DEFAULT '0',
`SickAccrualRate` tinyint(3) unsigned NOT NULL DEFAULT '0',
`VacationAccrualRate` tinyint(3) unsigned NOT NULL DEFAULT '0',
`PTOAccrualRate` tinyint(3) unsigned NOT NULL DEFAULT '0',
`AccruedSickHours` decimal(8,2) DEFAULT '0.00',
`AccruedVacationHours` decimal(8,2) DEFAULT '0.00',
`AccruedPTOHours` decimal(8,2) DEFAULT '0.00',
`LastSickAccrualDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`LastVacationAccrualDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`LastPTOAccrualDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`DateOfSickAccrualStart` date DEFAULT NULL,
`DateOfVacationAccrualStart` date DEFAULT NULL,
`DateOfPTOAccrualStart` date DEFAULT NULL,
`SickHoursAccruedPerYear` decimal(10,3) NOT NULL DEFAULT '0.000',
`VacationHoursAccruedPerYear` decimal(10,3) NOT NULL DEFAULT '0.000',
`PTOHoursAccruedPerYear` decimal(10,3) NOT NULL DEFAULT '0.000',
`MaxAccruableSickHours` decimal(10,3) unsigned NOT NULL DEFAULT '0.000',
`MaxAccruableVacationHours` decimal(10,3) unsigned NOT NULL DEFAULT '0.000',
`MaxAccruablePTOHours` decimal(10,3) unsigned NOT NULL DEFAULT '0.000',
`NewEmployerAnnouncement` tinyint(3) unsigned NOT NULL DEFAULT '0',
`NewTimeclockAnnouncement` tinyint(3) unsigned NOT NULL DEFAULT '0',
`AccountVerificationQuestion` varchar(80) NOT NULL DEFAULT '',
`AccountVerificationAnswer` varchar(50) NOT NULL DEFAULT '',
`FingerprintLoginOnly` tinyint(3) unsigned NOT NULL DEFAULT '0',
`EmployeeSignatureEnabled` tinyint(3) unsigned NOT NULL DEFAULT '0',
`EmailPreference` tinyint(3) unsigned NOT NULL DEFAULT '1',
`ClockInRestriction` tinyint(3) unsigned NOT NULL DEFAULT '0',
`ClockInRestrictStart` time NOT NULL DEFAULT '09:00:00',
`ClockInRestrictEnd` time NOT NULL DEFAULT '17:00:00',
`AutomaticShiftClockOut` tinyint(3) DEFAULT '0',
`MaxHoursAllowedPerDay` tinyint(3) DEFAULT '-1',
`SalesforceID` varchar(25) DEFAULT NULL,
`SalesforcePassword` char(32) DEFAULT NULL,
`AutomaticTimeDeductionType` tinyint(3) unsigned NOT NULL DEFAULT '0',
`AutomaticTimeDeductionAmount` tinyint(3) unsigned NOT NULL DEFAULT '0',
`AutomaticTimeDeductionMinHours` decimal(3,1) unsigned NOT NULL DEFAULT '8.0',
`DefaultHourlyAccountCodeID` int(11) unsigned NOT NULL DEFAULT '0',
`DefaultProjectAccountCodeID` int(11) unsigned NOT NULL DEFAULT '0',
`DefaultExpenseAccountCodeID` int(11) unsigned NOT NULL DEFAULT '0',
`DefaultProjectID` int(11) unsigned NOT NULL DEFAULT '0',
`OvertimePolicy` tinyint(3) unsigned DEFAULT '1',
`EmployeeWeekStart` int(10) unsigned DEFAULT '10000',
`MinimumHoursForWeeklyOvertime` tinyint(3) unsigned DEFAULT '40',
`MinimumHoursForDailyOvertime` tinyint(3) unsigned DEFAULT '8',
`MinimumHoursForDailyDoubletime` tinyint(3) unsigned DEFAULT '12',
`MinimumHoursForDailyWeekOvertime` tinyint(3) unsigned DEFAULT '40',
`SessionTimeout` tinyint(3) unsigned NOT NULL DEFAULT '20',
`LastChangeDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`LastChangeByID` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`user_id`),
KEY `ix_username` (`Username`),
KEY `ix_companystatus` (`CompanyID`,`UserStatus`),
KEY `ix_adminempstatus` (`AdminUserID`,`UserStatus`),
KEY `idx_useraccess` (`access`),
KEY `idx_jobtitle` (`JobTitle`(10)),
KEY `idx_companyid` (`CompanyID`),
KEY `idx_adminid` (`AdminUserID`),
KEY `idx_adminanduserid` (`AdminUserID`,`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=50176 DEFAULT CHARSET=utf8 PACK_KEYS=0;

--
-- Table structure for table records
--

CREATE TABLE `records` (
`RecordID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`UserID` int(11) unsigned NOT NULL DEFAULT '0',
`Hours` decimal(6,2) unsigned DEFAULT NULL,
`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(6,2) 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) DEFAULT NULL,
`TimezoneID` tinyint(3) unsigned DEFAULT '31',
`DSTEnabled` tinyint(3) 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) NOT NULL DEFAULT '1',
PRIMARY KEY (`RecordID`),
KEY `ix_unpaidhours` (`UserID`,`Paid`),
KEY `ix_accrualsum` (`TimeClass`,`Approved`),
KEY `ix_userid` (`UserID`),
KEY `idx_xhours` (`Paid`,`TimeClass`,`UserID`),
KEY `idx_accountcodeid` (`AccountCodeID`),
KEY `idx_recordalerts` (`UserID`,`Approved`,`Paid`),
KEY `idx_usertimeclass` (`UserID`,`TimeClass`)
) ENGINE=MyISAM AUTO_INCREMENT=3912659 DEFAULT CHARSET=utf8;

--
-- Table structure for table `accountcode`
--

CREATE TABLE `accountcode` (
`AccountCodeID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`CompanyID` int(11) unsigned NOT NULL DEFAULT '0',
`RequireAssignment` tinyint(3) unsigned NOT NULL DEFAULT '0',
`AccountCodeName` varchar(45) NOT NULL DEFAULT '',
`AccountCodeDescription` text,
`DefaultPayRate` decimal(8,2) NOT NULL DEFAULT '0.00',
`CreatorUserID` int(11) unsigned NOT NULL DEFAULT '0',
`CreatedDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`AccountCodeStatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
`ApplicationFlags` tinyint(3) unsigned NOT NULL DEFAULT '7',
`ReadOnly` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`AccountCodeID`),
KEY `ix_companyid` (`CompanyID`),
KEY `idx_accountcodeid` (`AccountCodeID`)
) ENGINE=MyISAM AUTO_INCREMENT=16162 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `records_splitpaid`
--

CREATE TABLE `records_splitpaid` (
`RecordID` int(11) unsigned NOT NULL DEFAULT '0',
`PayrollID` int(11) unsigned NOT NULL DEFAULT '0',
`ClockIn` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ClockOut` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`Hours` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
PRIMARY KEY (`RecordID`),
KEY `idx_payrollid` (`PayrollID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `label`
--

CREATE TABLE `label` (
`LabelID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`CompanyID` int(11) NOT NULL DEFAULT '0',
`TimeClass` tinyint(3) NOT NULL,
`LabelName` varchar(25) NOT NULL,
`LabelAbbrev` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`LabelID`),
KEY `idx_timeclasscompany` (`CompanyID`,`TimeClass`)
) ENGINE=MyISAM AUTO_INCREMENT=805 DEFAULT CHARSET=utf8;

--
-- Table structure for table `list_timezone`
--

CREATE TABLE `list_timezone` (
`timezone_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`timezone_nondst_gmt_offset` decimal(3,1) NOT NULL DEFAULT '0.0',
`timezone_dst_gmt_offset` decimal(3,1) NOT NULL DEFAULT '0.0',
`timezone_nondst_abbr` varchar(10) NOT NULL DEFAULT '',
`timezone_dst_abbr` varchar(10) NOT NULL DEFAULT '',
`timezone_description` varchar(255) NOT NULL DEFAULT '',
`Rank` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`timezone_id`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

HERE IS THE TABLE STATUS:

+--------------------------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+------------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------------------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+------------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------------------------------------------+
| accountcode | MyISAM | 10 | Dynamic | 15038 | 63 | 952664 | 281474976710655 | 467968 | 0 | 16162 | 2011-01-17 17:22:30 | 2011-01-26 20:28:47 | 2011-01-26 23:45:03 | utf8_general_ci | NULL | | |
| label | MyISAM | 10 | Dynamic | 352 | 25 | 9020 | 281474976710655 | 10240 | 0 | 805 | 2011-01-17 17:25:50 | 2011-01-26 20:30:30 | 2011-01-26 23:45:23 | utf8_general_ci | NULL | | |
| records | MyISAM | 10 | Dynamic | 3800072 | 101 | 385146036 | 281474976710655 | 326663168 | 0 | 3912659 | 2011-01-26 00:44:04 | 2011-01-26 20:30:54 | 2011-01-26 23:37:32 | utf8_general_ci | NULL | | |
| records_splitpaid | MyISAM | 10 | Fixed | 65 | 29 | 1885 | 8162774324609023 | 3072 | 0 | NULL | 2011-01-17 17:38:31 | 2011-01-26 20:32:54 | 2011-01-26 23:47:09 | utf8_general_ci | NULL | | |
| users | MyISAM | 10 | Dynamic | 35873 | 262 | 9426084 | 281474976710655 | 4066304 | 0 | 50176 | 2011-01-17 17:38:34 | 2011-01-26 20:32:54 | 2011-01-26 23:47:10 | utf8_general_ci | NULL | pack_keys=0 | |
+--------------------------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+------------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------------------------------------------+

HERE ARE THE BUFFER VARIABLES:

+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 4294967296 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 4294967296 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 16777216 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 16777216 |
| sql_buffer_result | OFF |
+------------------------------+------------+