MySQL Forums
Forum List  »  Performance

Re: Query Takes Longer Each Time Its Run
Posted by: anthony
Date: January 27, 2011 02:35PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3850
January 26, 2011 02:59PM
Re: Query Takes Longer Each Time Its Run
1462
January 27, 2011 02:35PM
1542
January 27, 2011 10:59PM
1987
January 28, 2011 01:38PM
1336
January 30, 2011 07:20PM
1376
January 31, 2011 01:22AM
1491
January 31, 2011 01:25AM
1344
February 01, 2011 12:38AM
1458
February 01, 2011 12:56AM
1403
February 01, 2011 01:23PM
1638
February 01, 2011 08:41PM
2094
February 03, 2011 02:04PM
1594
February 15, 2011 09:24PM


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.