I am using MySQL 5.6.
In my table
invoices I added two datetime columns that I will be setting, based on if another column is a certain value.
UPDATE invoices SET twoWeekAlert = DATE_ADD(now(), INTERVAL 2 WEEK) WHERE state = 6;
There are only 205 records that have state =6 and 3,500 total records.
After 5 minutes I canceled the query, made an index on the state column, and tried again. After 10 minutes I canceled that one.
What is going on, is this a known issue with MySQL updating using a datetime calculation function or something? I worry because I know I will have to run similar type updates in the future and I can't have it take that long.
EXPLAIN statement I believe is telling me it IS using my index:
selectType table type possible_keys key key_len ref rows extra SIMPLE invoices range stateIndex stateindex 2 const 205 using where
There were no warnings in my explain statement.
CREATE statement of my table
CREATE TABLE `invoices` ( `idx` int(11) NOT NULL AUTO_INCREMENT, `number` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `parentSOId` int(11) DEFAULT NULL, `parentProjectId` int(11) DEFAULT NULL, `status` int(1) DEFAULT '1', `active` int(1) DEFAULT '1', `dateEntered` varchar(45) DEFAULT NULL, `dateDue` varchar(45) DEFAULT NULL, `individualId` int(11) DEFAULT '-1', `amount` decimal(11,2) DEFAULT '0.00', `margin` decimal(11,2) DEFAULT '0.00', `comment` varchar(500) DEFAULT '', `custContactId` int(11) DEFAULT '-1', `custBuyerId` int(11) DEFAULT '-1', `taxable` int(11) DEFAULT NULL, `taxAmount` decimal(11,2) DEFAULT NULL, `totalAmount` decimal(11,2) DEFAULT NULL, `paymentTerms` int(11) DEFAULT '-1', `type` int(11) DEFAULT '-1', `shipVia` int(11) DEFAULT '-1', `manTax` int(1) DEFAULT '0', `state` tinyint(4) DEFAULT '0', `sentToContNotNeeded` int(1) DEFAULT '0', `sentToAcctNotNeeded` int(1) DEFAULT '0', `twoWeekAlert` datetime DEFAULT NULL, `threeWeekAlert` datetime DEFAULT NULL, PRIMARY KEY (`idx`), KEY `invoiceNum` (`number`), KEY `invoiceName` (`name`), KEY `fk-listprojects_idx` (`parentProjectId`), KEY `soIdIndex` (`parentSOId`), KEY `stateInd` (`state`), CONSTRAINT `fk-listprojects` FOREIGN KEY (`parentProjectId`) REFERENCES `listprojects` (`idx`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=3604 DEFAULT CHARSET=latin1;
Things I've also tried:
Doing it by primary key ie
WHERE idx IN (list of ids)
Instead of using datetime, just using date since it is enough for my needs and using
DATE_ADD(curDate(), INTERVAL 2 WEEK)
I've tried both ways:
NOW() with a datetime type and
curDate() with a date type. Both had the same issue.
Running it on a much stronger machine. Still the same issue.
Luckily I was just told to not retroactively affect old records so I will only be doing one record at a time, which seemed to work. I am still very curious why this seems to take forever especially when I am the only connection on a dev database.
My server is local, in office, running on a machine in the closet. So, bare metal, not cloud or ESX.
SELECT IFNULL(state,'Total') state_value, COUNT(1) rowcount FROM invoices GROUP BY state WITH ROLLUP;
-1 14 0 3217 2 5 4 54 5 9 6 205 Total 3504
SHOW INDEX ON invoices results:
Table - invoices Non_Unique - 1 Key_Name - stateIndex Seq_in_Index - 1 (Same for all other indexes) Column_name - state Collation - A (all other indexes have an A for this) Cardinality - 12 (All other indexes have a Cardinality of 3476) Sub_part - Null (Same for all other indexes) Packed - Null (Same for all other indexes) Null - YES (Same for all other indexes) Index_type - BTREE (Same for all other indexes)