85260


explain で  
'Using where; Using join buffer (Block Nested Loop)'  
というのがでて INDEX を使ってくれません

<https://nishinatoshiharu.com/overview-nested-loopjoin/>  
検索するとこういう記事が出てきてINDEXをつけると治るとかいてあるんですが元からついています

* * *

該当のクエリ

[code]

    EXPLAIN SELECT 
      user_id, target_id, u.x, u.y 
    FROM targets t
    JOIN user_positions u USING(user_id)
    WHERE state = 0
    AND u.x IS NOT NULL
    AND (u.x - t.x) * (u.x - t.x) + (u.y - t.y) * (u.y - t.y) < 1.0
    
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
    |  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  52245 |   100.00 | NULL                                               |
    |  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 680924 |     9.00 | Using where; Using join buffer (Block Nested Loop) |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
    2 rows in set, 1 warning (0.00 sec)
    
[/code]

targets はデバッグようにテーブルにしてますが  
実際はサブクエリなのでインデックスはつけられません  
調べるべき user_id と座標情報を持っています

[code]

    CREATE TABLE `targets` (
      `user_id` varchar(255) NOT NULL,
      `target_id` int(11) NOT NULL DEFAULT '0',
      `x` double DEFAULT NULL,
      `y` double DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
[/code]

user_positions は user_id ごとに座標と状態情報を持ったテーブルで  
user_id にユニークインデックスを貼っています

[code]

    CREATE TABLE `user_positions` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` varchar(255) NOT NULL,
      `x` decimal(9,6) NOT NULL,
      `y` decimal(9,6) NOT NULL,
      `state` int(11) NOT NULL DEFAULT '1',
      `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `index_user_positions_on_user_id` (`user_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
    
[/code]

サブクエリで計算された targets が 4 万件程度で  
user_positions は 100万件ほどレコードがあります

`join user_positions USING(user_id) `  
ブログでいうところの targets が駆動テーブル user_positions が内部テーブルで  
で INDEX のついた user_id で join しているので INDEX がきくはずなんで  
4万件程度の距離計算なら一瞬で終わるかなと思ったんですが  
なぜかこれが数分経っても終わりません

実行計画を見たところ  
`Using where; Using join buffer (Block Nested Loop)`  
というのがあってインデックスが使われていませんでした

どうすれば解決できるのでしょうか


CC BY-SA 4.0
2021-12-21T15:21:59.340
85258
2021-12-27T12:34:12.420
2021-12-27T12:34:12.420
2808
question
mysql sql
'Using where; Using join buffer (Block Nested Loop)'の解決方法


`CHARSET` が異なるため index を利用できていないように見えます。

例えば [`convert()`](https://dev.mysql.com/doc/refman/5.6/ja/charset-
convert.html) で `targets.user_id` を `latin1` に変換すると index が利用されました。

[code]

    SELECT 
      t.user_id, target_id, u.x, u.y 
    FROM targets t
    JOIN user_positions u on convert(t.user_id using latin1) = u.user_id
    WHERE state = 0
    AND u.x IS NOT NULL
    AND (u.x - t.x) * (u.x - t.x) + (u.y - t.y) * (u.y - t.y) < 1.0
    ;
    
[/code]

比較: <http://www.sqlfiddle.com/#!9/0ca174/9>


CC BY-SA 4.0
2021-12-21T17:59:02.340
85260
2021-12-21T18:18:22.573
2021-12-21T18:18:22.573
2808
2808
85258
answer