mysql-straight-join.md

データボリューム

table_A：　1,000,000件

table_B：　300,000件

STRAIGHT_JOINを使った集約関数クエリ

SELECT
    COUNT(*) AS Num
  FROM
    table_A AS A
    STRAIGHT_JOIN table_B AS B
      ON A.ID = B.ID

実行プラン

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	ALL	Index_B_ID	NULL	NULL	NULL	1086471	Using where
1	SIMPLE	B	eq_ref	PRIMARY	PRIMARY	4	A.ID	1	Using where

PROFILE

starting 0.000051
checking permissions 0.000004
checking permissions 0.000005
Opening tables 0.000016
System lock 0.000007
init 0.000017
optimizing 0.000011
statistics 0.000021
preparing 0.000010
executing 0.000005
Sending data 2.728493
end 0.000014
query end 0.000005
closing tables 0.000008
freeing items 0.000036
logging slow query 0.000003
cleaning up 0.000003

INNER JOINを使った集約関数クエリ

SELECT
    COUNT(*) AS Num
  FROM
    table_A AS A
    INNER JOIN table_B AS B
      ON A.ID = B.ID

実行プラン

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	B	ALL	PRIMARY	NULL	NULL	NULL	276310	Using where
1	SIMPLE	A	ref	Index_B_ID	Index_B_ID	4	B.ID	1	Using where

PROFILE

starting 0.000078
checking permissions 0.000005
checking permissions 0.000004
Opening tables 0.000024
System lock 0.000008
init 0.000027
optimizing 0.000016
statistics 0.000029
preparing 0.000014
executing 0.000005
Sending data 15.625788
end 0.000012
query end 0.000004
closing tables 0.000007
freeing items 0.000017
logging slow query 0.000003
logging slow query 0.000029
cleaning up 0.000004