Why the JOIN doesn't use index?

Question

I have a query like this:

EXPLAIN SELECT dictionary.id, dictionary.word, lwd.grammatical_role, lwd.phonetics
FROM loghatnameh_dehkhoda dictionary
LEFT JOIN loghatnameh_words_details lwd ON lwd.word = dictionary.word 
WHERE dictionary.word= "سلام" and dictionary.id= 321780 LIMIT 1

Also, here is the result of EXPLAIN:

enter image description here

As you can see, that join is not optimized. Since a full scan happened and 80147 rows scanned. It should be noted, I have an index on both columns on the join. I mean loghatnameh_dehkhoda(word) and loghatnameh_words_details(word) columns. But still no index get used (apparently)

Noted that those two tables/columns are: (not sure if it's related to the topic)

- `loghatnameh_dehkhoda => MyISAM / utf8mb4_unicode_ci
- loghatnameh_words_details => InnoDB / utf8_bin

Any idea how can I make it more optimal?

• Dai: Post all of the CREATE INDEX statements on your loghatnameh_words_details and loghatnameh_dehkhoda tables.
• Dai: Is there a reason you're doing a LEFT OUTER JOIN instead of an INNER JOIN?

Answer 1

When JOINing on a VARCHAR, the INDEX will not be used unless both the CHARACTER SET and COLLATION are the same between the tables. (The ENGINE is not critical. But, I would recommend moving all your tables to InnoDB for other reasons.)

These indexes may be useful:

dictionary: INDEX(word, id)
lwd: INDEX(word, grammatical_role, phonetics)

Is there some reason for having two separate tables? The column names sound like things that might be in a single table with PRIMARY KEY(word).

I am puzzled by the WHERE clause testing both word and id. Please explain.

Is the LIMIT 1 necessary? If there can be more than one row, which one do you want? (Perhaps you need an ORDER BY?)