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.

The 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.

The 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;

Results:

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