0

I have two tables PARENT and CHILD. PARENT has one to many relationship with CHILD. PARENT table has 300000 and CHILD has 1500000 rows respectively. Row with some ID is inserted and deleted frequently and deletion takes time approximately 20 sec. And Execution Plan of deletion in CHILD table shows entire table scan. Why deletion on indexed field takes time?

UPDATE - 1

Table schema of parent and child

show create table parent;

| Table  | Create Table                                                                                                                                                                                            | parent | CREATE TABLE `parent` (
  `id` bigint(20) NOT NULL,
  `name` varchar(500) COLLATE latin1_general_cs DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs |
show create table child;

| Table | Create Table                                                                                                                                                                                                                                                                                             | child | CREATE TABLE `child` (
  `id` bigint(20) NOT NULL,
  `value` varchar(2000) COLLATE latin1_general_cs DEFAULT NULL,
  KEY `child_cons` (`id`),
  CONSTRAINT `child_cons` FOREIGN KEY (`id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs |

UPDATE - 2

The delete statement used is 

delete from child where id=1;

Execution plan

explain delete from child where id=1; 

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |

|  1 | SIMPLE      | child | ALL  | child_cons    | NULL | NULL    | NULL | 1350398 | Using where |

1 row in set (0.00 sec)

UPDATE - 3

Child table contains only 5 rows for id = 1

Abichellam
  • 509
  • 2
  • 7
  • 17
  • 2
    update your question and add your tables schema – ScaisEdge May 06 '19 at 09:45
  • 1
    Why deletion of indexed field takes time? Because it has to be found on disk and deleted perhaps? You didn't show the `DELETE` statement you used nor execution plan. – Mjh May 06 '19 at 10:09
  • 1
    Judging by what you posted, there's ~1,3 million records in `child` that have `id` value of `1`. MySQL forces the disk to *write* and confirm the write. In this case, it's a delete but it incurs an I/O action. – Mjh May 06 '19 at 12:10
  • 1
    Regarding the cost of DELETE operations on InnoDB, see this quite old answer. https://stackoverflow.com/a/830599/1252368 – Daniel Schneller May 06 '19 at 12:16
  • How many rows in each table? How many different values of `id` in `child`? – Rick James May 06 '19 at 18:41
  • See if this changes the `EXPLAIN`: `ANALYZE TABLE table2;` – Rick James May 06 '19 at 18:43
  • @RickJames 300000 rows in PARENT Table and 1500000 rows in CHILD. CHILD has 300000 different values for ID in CHILD table – Abichellam May 07 '19 at 05:52
  • `ANALYZE` is really fast for InnoDB, and it seems like the best bet. – Rick James May 07 '19 at 06:15

0 Answers0