CODEWITHSUNDEEP
mysqljoinindexing
alsky
alsky

Reputation: 325

LEFT JOIN is fast but RIGHT JOIN is slow even though the same indexes are on both tables

I have two tables, both with around 200,000 records. I want to join them on KEY which is a string.

Both tables have an index KEY, VALUE. When I run: 

   SELECT vpn, t1_sku, t2_sku 
     FROM first_inventory 
LEFT JOIN second_inventory USING (vpn)

It is really fast. Here is the explain output:

 ----------------------------------------------------------------------------------------------------------------------------
 | id | select_type | table            | type     | possible_keys   | key     | key_len   | ref     | rows   | Extra        |
 |----|-------------|------------------|----------|-----------------|---------|-----------|---------|--------|--------------|
 | 1  | SIMPLE      | first_inventory  | index    |                 | t1_vpn  | 158       |         | 204437 | Using index  |
 | 1  | SIMPLE      | second_inventory | ref      | t2_vpn          | t2_vpn  | 387       | func    | 1      | Using index  |
 ----------------------------------------------------------------------------------------------------------------------------

However, when I do a RIGHT JOIN it takes forever and never finishes: "SELECT vpn, t1_sku, t2_sku FROM first_inventory RIGHT JOIN second_inventory USING (vpn)". Here is the explain output:

 ----------------------------------------------------------------------------------------------------------------------------
 | id | select_type | table            | type     | possible_keys   | key     | key_len   | ref     | rows   | Extra        |
 |----|-------------|------------------|----------|-----------------|---------|-----------|---------|--------|--------------|
 | 1  | SIMPLE      | second_inventory | index    |                 | t1_vpn  | 773       |         | 220996 | Using index  |
 | 1  | SIMPLE      | first_inventory  | index    |                 | t2_vpn  | 158       |         | 204437 | Using index  |
 ----------------------------------------------------------------------------------------------------------------------------

I can see that the RIGHT JOIN query appears to be looking at 220996 x 204437 rows, while the LEFT JOIN only seems to be looking at 204437 rows, but I can't figure out why since I've made the same indexes on both tables.

At the end of both tables it says:

PRIMARY KEY ('id'),
  KEY 't1_vpn' ('vpn','t1_sku') USING BTREE
and
PRIMARY KEY ('id'),
  KEY 't2_vpn' ('vpn','t2_sku') USING BTREE

Any idea why the RIGHT JOIN query is taking so long?

Upvotes: 2

Views: 513

Answers (1)

alsky
alsky

Reputation: 325

The problem was that there was UTF-8 content in one of the tables. I changed everything to Latin1 and it runs fast both ways now.

See: this discussion

Upvotes: 1

Related Questions