Incredibly long execution time for a update query?

Question

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)

Answer 1

Your problem is simple

PROBLEM #1 : KEY DISTRIBUTION

The MySQL Query Optimizer evaluates the best course of action and publishes it in the EXPLAIN plan. Note that the EXPLAIN plan says selectType SIMPLE while using the index on the state. That is an index scan.

I have a basic rule-of-thumb I always go by : If an index has to read more that 5% of a table while perusing an index, it starts doing scans. You are basically at a tipping point. Why ???

• 205 rows to be updated
• 3504 total rows
• 5.85% of the table rows being read/updated

I am not just coming up with this. I have always mentioned this in the DBA StackExchange (Se my post from Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY? and the other posts mentioned in it)

PROBLEM #2 : CONFIGURATION OF HARDWARE

The table is way too small to say it's just indexing. If the hardware you are using is small (1 vCPU), don't expect performance of any kind with MySQL 5.6. There are corner cases where MySQL 4.1 would outperform MySQL 5.6 in a single CPU environment. You are probably in that corner right now.

See my posts

• Apr 26, 2012 : Is the CPU performance relevant for a database server?
• Jun 19, 2011 : How do I properly perform a MySQL bake-off?

WHERE TO GO FROM HERE ...

You best bet at this point would be to run OPTIMIZE TABLE invoices;. This will shrink that table (eliminating fragmentation) and refresh the index stats. In a high-write production environment, you could run ANALYZE TABLE invoices; to refresh index stats only and run OPTIMIZE TABLE invoices; during a maintenance window.

Also, try setting up more that 1 CPU for test servers.

GIVE IT A TRY !!!