4

This query takes almost 2 minutes to execute (changing 9 records):

UPDATE table1 t1
SET t1.code_id = null, t1.code_group = null
WHERE t1.another_id IN (SELECT t2.another_id 
                        FROM table2 t2
                        WHERE ((t2.id_parent = 2658 AND t2.year = 2016) 
                               OR (t2.id = 2658 AND t2.year = 2016)))

Executing this query alone takes 0.0030s:

SELECT t2.another_id 
FROM table2 t2
WHERE ((t2.id_parent = 2658 AND t2.year = 2016) 
       OR (t2.id = 2658 AND t2.year = 2016))

and returns 3 rows in form of a integer.

Here is info about both tables:

CREATE TABLE IF NOT EXISTS `table1` 
(
  `another_id` int(11) NOT NULL,
  `table1_id` int(11) NOT NULL,
  `code_group` varchar(1) DEFAULT NULL,
  `code_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`another_id`,`table1_id`),
  KEY `another_id` (`another_id`),
  KEY `code_group` (`code_group`,`code_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `table2` 
(
  `id_year` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `id_parent` int(11) DEFAULT NULL,
  `another_id` int(11) NOT NULL,
  `code_group` varchar(1) DEFAULT NULL,
  `code_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id_year`,`id`),
  KEY `id_parent` (`id_year`,`id_parent`)
  KEY `another_id` (`another_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_polish_ci;

Is there anyone, who can tell my why it needs 2 minutes to execute this query?

Improve this question
6
  • why ((t2.id_parent= 2658 AND t2.year= 2016) two times in or? Commented Sep 2, 2016 at 9:45
  • 2
    use Explain to see where you Need an index Commented Sep 2, 2016 at 9:45
  • 1
    From my experience MySQL sometimes seems to have problems optimizing unrelated subqueries, so that they seem to get executed for each row again. In your case I would suggest run the subselect in a separate query and then create the actual query with just the results dynamically in your application. Commented Sep 2, 2016 at 9:47
  • Try to use Index for optimize it. CREATE NONCLUSTERED INDEX... Commented Sep 2, 2016 at 9:48
  • table2 does not have a column t2.another_id. MySQL will join over it, so it is relevant which column it is. Commented Sep 2, 2016 at 9:52

2 Answers 2

Reset to default
1

You can use INNER JOIN to update as following:t2.year is also not exist

UPDATE table1 t1
INNER JOIN table2 t2 ON t2.another_id = t1.another_id
    AND ((t2.id_parent= 2658 AND t2.year= 2016) OR (t2.id= 2658 AND t2.year= 2016))
SET t1.code_id = NULL, t1.code_group = NULL
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hey, I am unable to execute this query. MySQL returns sytax error at line 2.
I have updated the query according to MYSQL BTW I am not getting year column in T2.
1

IN can sometimes impede optimization. I would start by putting the subquery directly in the FROM clause:

UPDATE table1 t1 JOIN
       (SELECT t2.another_id 
        FROM table2 t2
        WHERE ((t2.id_parent= 2658 AND t2.year= 2016) OR
               (t2.id= 2658 AND t2.year= 2016)
              )
       ) t2
       ON t1.another_id = t2.another_id
    SET t1.code_id = null,
        t1.code_group = null;

Then, looking at this query, I would recommend an index on table1(another_id). In fact, that index might be sufficient for your original query.

Improve this answer

2 Comments

While I still cannot find reasonable answer for main question (there was already index for "another_id" on both tables) , your solution works like a charm. Turning 2 minutes query into 0.0040s one. Thanks;)
@JacMar . . . The answer to your question is that IN sometimes impedes optimization.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.