1

I have a database with two tables that I would consider small. I need to update values on the larger of the two tables (1,100,000 rows, 75M MYD, 120M MYI) from the join of the two tables. The smaller of the two tables is (85,000 rows, 15M MYD, 11M MYI). Using EXPLAIN I've added indexes so that the SELECT that mirrors the UPDATE says it's using indexes on the larger of the two tables.

The UPDATE I'm trying to perform:

UPDATE membership M, 
    cover_details CD
SET M.option_id = CD.option_id,
    M.depind = if(CD.dependent_type=17, "P", "D"),
    M.cover_detail_id = CD.cover_detail_id,
    M.entity_common_id = CD.entity_common_id
WHERE M.runmonth BETWEEN CD.cover_start_date AND CD.cover_end_date
    AND M.memnum = CD.cover_number
    AND M.benfcnum = CD.dependent_number ;

The EXPLAIN:

+------+-------------+-------+------+------------------------------+----------+---------+-----------------------------------------------------------+-------+--------------------------+
| id   | select_type | table | type | possible_keys                | key      | key_len | ref                                                       | rows  | Extra                    |
+------+-------------+-------+------+------------------------------+----------+---------+-----------------------------------------------------------+-------+--------------------------+
|    1 | SIMPLE      | CD    | ALL  | membership_idx,test          | NULL     | NULL    | NULL                                                      | 84106 | Using where              |
|    1 | SIMPLE      | M     | ref  | MemNum,MemNum_2,runmonth_idx | MemNum_2 | 18      | xxxxxxxxxx.cd.COVER_NUMBER,xxxxxxxxxx.cd.DEPENDENT_NUMBER |  1355 | Using where; Using index |
+------+-------------+-------+------+------------------------------+----------+---------+-----------------------------------------------------------+-------+--------------------------+

This query hasn't completed in 24 hours. The database is on an SSD too. What am I missing, surely this should only take a couple of minutes?

EDIT: Here are the two tables create statements:

-- The small table
CREATE TABLE `cover_details` (
  `COVER_DETAIL_ID` int(11) NOT NULL,
  `PRODUCT_ID` int(11) NOT NULL,
  `OPTION_ID` int(11) NOT NULL,
  `ENTITY_COMMON_ID` int(11) DEFAULT NULL,
  `COVER_NUMBER` int(11) DEFAULT NULL,
  `DEPENDENT_NUMBER` int(11) DEFAULT NULL,
  `COVER_PREMIUM_AMOUNT` decimal(10,2) DEFAULT NULL,
  `DEPENDENT_TYPE` int(11) DEFAULT NULL,
  `PENALTY_JOIN` double DEFAULT NULL,
  `COVER_START_DATE` varchar(21) DEFAULT NULL,
  `COVER_END_DATE` varchar(21) DEFAULT NULL,
  `APPLICATION_RECEIVE_DATE` varchar(21) DEFAULT NULL,
  `STATUS_REASON` int(11) DEFAULT NULL,
  `SECURITY_GROUP_ID` int(11) DEFAULT NULL,
  `EFFECTIVE_START_DATE` varchar(21) DEFAULT NULL,
  `EFFECTIVE_END_DATE` varchar(21) DEFAULT NULL,
  `CREATED_BY` int(11) DEFAULT NULL,
  `CREATION_DATE` varchar(21) DEFAULT NULL,
  `LAST_UPDATED_BY` int(11) DEFAULT NULL,
  `LAST_UPDATE_DATE` varchar(21) DEFAULT NULL,
  KEY `membership_idx` (`COVER_NUMBER`,`DEPENDENT_NUMBER`),
  KEY `pk_idx` (`COVER_DETAIL_ID`),
  KEY `ec_join_idx` (`ENTITY_COMMON_ID`,`COVER_START_DATE`,`COVER_END_DATE`,`EFFECTIVE_START_DATE`,`EFFECTIVE_END_DATE`,`PRODUCT_ID`),
  KEY `test` (`COVER_START_DATE`,`COVER_END_DATE`,`COVER_NUMBER`,`DEPENDENT_NUMBER`),
  KEY `test2` (`COVER_START_DATE`,`COVER_END_DATE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

-- the big table
CREATE TABLE `membership` (
  `MemNum` bigint(20) DEFAULT NULL,
  `currmemnum` int(11) DEFAULT NULL,
  `charcurrmemnum` varchar(45) DEFAULT NULL,
  `BenfcNum` bigint(20) DEFAULT NULL,
  `option_id` int(11) DEFAULT NULL,
  `ProdCode` varchar(10) DEFAULT NULL,
  `inccat` varchar(10) DEFAULT NULL,
  `broker` int(11) DEFAULT NULL,
  `runMonth` date DEFAULT NULL,
  `Status` varchar(10) DEFAULT NULL,
  `depInd` varchar(1) DEFAULT NULL,
  `age` double DEFAULT NULL,
  `gndr` varchar(5) DEFAULT NULL,
  `yerun` int(11) DEFAULT NULL,
  `morun` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NoPayInd` int(11) DEFAULT NULL,
  `GrpType` varchar(30) DEFAULT NULL,
  `RegDate` varchar(30) DEFAULT NULL,
  `ResignDate` varchar(30) DEFAULT NULL,
  `monthlyRiskRaised` double DEFAULT NULL,
  `monthlyLJPRaised` double DEFAULT NULL,
  `monthlyMSARaised` double DEFAULT NULL,
  `monthlyInvoiced` double DEFAULT NULL,
  `monthlyPaid` double DEFAULT NULL,
  `monthsTillResig` int(11) DEFAULT NULL,
  `monthsSinceReg` int(11) DEFAULT NULL,
  `empbranch` int(11) DEFAULT NULL,
  `empgroup` int(11) DEFAULT NULL,
  `has_claims_data` varchar(10) DEFAULT 'Yes',
  `entity_common_id` int(11) DEFAULT NULL,
  `cover_detail_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `MemNum` (`MemNum`,`BenfcNum`,`yerun`,`morun`),
  KEY `MemNum_2` (`MemNum`,`BenfcNum`,`runMonth`),
  KEY `ProdCode` (`ProdCode`),
  KEY `charcurrmemnum` (`charcurrmemnum`),
  KEY `empbranch` (`empbranch`),
  KEY `empgroup` (`empgroup`),
  KEY `broker` (`broker`),
  KEY `runmonth_idx` (`runMonth`)
) ENGINE=MyISAM AUTO_INCREMENT=1093805 DEFAULT CHARSET=utf8
  • It would help of you added the actual UPDATE statement and the output of SHOW CREATE TABLE tablename; for both tables. – ypercubeᵀᴹ Aug 3 '15 at 13:27
  • @ypercube: Done – sirlark Aug 3 '15 at 13:38
  • How many rows will be updated? 30% or 90% or all? If it is going to be a big part of the table then using full table scan on the M table is probably preferable as now it does a full scan on CD and then randomly jumps around Mto find matching rows. – jkavalik Aug 3 '15 at 14:38
  • 90% plus definitely. So I suppose the next question is how do I switch it round to do a full table scan on M? – sirlark Aug 3 '15 at 14:40
  • As you have the condition M.runmonth BETWEEN CD.cover_start_date AND CD.cover_end_date the optimizer has not much chances to do that one well the other way around. But if you can do a covering index for CD (Using index in equivalent select plan) and probably remove/ignore the index used on M so optimzer will not have a chance to go that way. For testing purposes I use SELECT [STRAIGHT_JOIN](https://dev.mysql.com/doc/refman/5.5/en/join.html) .. but it is better to modify the query/indexes so the right plan is used even without it. – jkavalik Aug 3 '15 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.