mysql - 如何通过连接字符串来加速 INSERT SELECT

Question

我想调用 insert select 并尝试使用此 select(借助此 INSERT SELECT query when one column is unique )

SELECT minids.userid, username, password, full_name, country, email,
      (select openclipart_files.id from aiki_users, openclipart_files
       where aiki_users.userid = users.userid and avatar like '%svg' AND
             filename = avatar) as avatar,
      homepage, usergroup, notify, nsfwfilter
FROM aiki_users users
INNER JOIN (SELECT MIN(userid) as userid FROM aiki_users GROUP by username) 
           minids ON minids.userid = users.userid;

我将它与插入一起使用

INSERT INTO openclipart_users(id, user_name, password, full_name, country,
email, avatar, homepage, user_group, notify, nsfw_filter) SELECT ...

执行时间很长(几分钟后我取消了)

aiki_users 表有 100k 行，openclipart_files 有 30k 行，基本上我只是将所有内容从 aiki_users 复制到 openclipart_users 跳过重复项，我希望头像是文件的 ID，其中旧表是一个字符串(只有大约 300 个用户有 svg大约 1k 用户的头像没有''头像，但我只有什么 svg)。

有什么方法可以快速将该头像(没有它 INSERT SELECT 在几秒钟内执行)插入到 openclipart_users 中，任何可行的解决方案都可以(快速我的意思是不到一分钟)。

EDIT 通过 SELECT 解释的输出

+----+--------------------+-------------------+--------+---------------+----------+---------+----------------------------------+--------+-------------+| id | select_type        | table             | type   | possible_keys | key      | key_len | ref                              | rows   | Extra       |+----+--------------------+-------------------+--------+---------------+----------+---------+----------------------------------+--------+-------------+|  1 | PRIMARY            | <derived3>        | ALL    | NULL          | NULL     | NULL    | NULL                             | 106689 |             ||  1 | PRIMARY            | users             | eq_ref | PRIMARY       | PRIMARY  | 4       | minids.userid                    |      1 |             ||  3 | DERIVED            | aiki_users        | index  | NULL          | username | 302     | NULL                             | 111273 | Using index ||  2 | DEPENDENT SUBQUERY | openclipart_files | ALL    | NULL          | NULL     | NULL    | NULL                             |  37715 |             ||  2 | DEPENDENT SUBQUERY | aiki_users        | eq_ref | PRIMARY       | PRIMARY  | 4       | openclipart_staging.users.userid |      1 | Using where |+----+--------------------+-------------------+--------+---------------+----------+---------+----------------------------------+--------+-------------+

Answer 1

转换为仅连接语法(摆脱相关子查询并改为连接到子查询):

SELECT minids.userid, username, password, full_name, country, email,
      clip.id as avatar,
      homepage, usergroup, notify, nsfwfilter
FROM aiki_users users
INNER JOIN (SELECT MIN(userid) as userid FROM aiki_users GROUP by username) 
           minids ON minids.userid = users.userid
LEFT OUTER JOIN openclipart_files clip ON 
           clip.owner = users.userid AND RIGHT(users.avatar, 3) = 'svg' 
           AND clip.filename = users.avatar

尝试一下。