0

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?

2
  • Post all of the CREATE INDEX statements on your loghatnameh_words_details and loghatnameh_dehkhoda tables.
    – Dai
    Nov 14 '21 at 7:09
  • Is there a reason you're doing a LEFT OUTER JOIN instead of an INNER JOIN?
    – Dai
    Nov 14 '21 at 7:10
0

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?)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.