mysql too long query

Question

Hi,

i have a problem with this mysql query. It take vmore as 1 day to execute ...

The query is :

INSERT INTO traduction  
(traduction.`id`,traduction.`traduction`,traduction.`language`,traduction.`type`) 
(
 SELECT cities.id,cities.name, '', 'city' FROM cities 
  WHERE cities.id NOT IN 
   (
    SELECT traduction.`id` FROM traduction WHERE traduction.type='city' GROUP BY id
   )
);

I made a explain extended on the 2 select and it says DEPENDENT SUBQUERY so the second select is played for every select on cities but I think it's useless.

There is a way in mysql or another sql server which can allow that ?

Or maybe a complet other query.

The idea is, if a city doesn't have a traduction, the city name should be writen in the traduction table. Then I just have to look in traduction table and not city table.

Answer 1

You are modifying traduction table, that's why the results of inner subquery cannot be reused

Answer 2

You can try something like this

INSERT INTO traduction   
(traduction.`id`,traduction.`traduction`,traduction.`language`,traduction.`type`)  
( 
SELECT  cities.id,
        cities.name, 
        '', 
        'city' 
FROM    cities  LEFT JOIN
        (
            SELECT  traduction.`id` 
            FROM    traduction 
            WHERE   traduction.type='city' 
            GROUP BY id 
        ) s ON cities.id = s.id
WHERE   s.ID IS NULL
);

Also ensure that you have the correct indexes on your tables, for lets say traduction.type or traduction.id and cities.id

INSERT INTO traduction   
(traduction.`id`,traduction.`traduction`,traduction.`language`,traduction.`type`)  
( 
SELECT  cities.id,
        cities.name, 
        '', 
        'city' 
FROM    cities  LEFT JOIN
        (
            SELECT  DISTINCT
                    traduction.`id` 
            FROM    traduction 
            WHERE   traduction.type='city'
        ) s ON cities.id = s.id
WHERE   s.ID IS NULL
);

EDIT: NOT EXISTS

INSERT INTO traduction    
(traduction.`id`,traduction.`traduction`,traduction.`language`,traduction.`type`)   
(  
SELECT  cities.id, 
        cities.name,  
        '',  
        'city'  
FROM    cities 
WHERE   NOT EXISTS (
                        SELECT  DISTINCT 
                                traduction.`id`  
                        FROM    traduction  
                        WHERE   traduction.type='city'
                        AND     cities.id = traduction.id 
                    )
);