mysql查询太长

发布于 2024-08-22 01:26:30 字数 562 浏览 4 评论 0原文

我对这个 mysql 查询有问题。它需要 vmore 作为 1 天来执行...

查询是：

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
   )
);

我对 2 个选择进行了扩展解释，它说“依赖子查询”，因此第二个选择会为城市上的每个选择播放，但我认为它没有用。

mysql 或其他 sql 服务器中有一种方法可以允许这样做吗？

或者也许是一个完整的其他查询。

这个想法是，如果一个城市没有翻译，则应该将城市名称写在翻译表中。然后我只需要查看翻译表而不是城市表。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

紫罗兰の梦幻 2024-08-29 01:26:30

您可以尝试这样的方法

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
);

还要确保表上有正确的索引，例如traduction.type或traduction.id和city.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
);

编辑：不存在

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 
                    )
);

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 
                    )
);

回复收藏 0 原文