Member Avatar for cy163

Hello ALL,

I did the following query. the EXPLAIN result showed 'NULL' in the 'key' colume, meaning that NONE of indexes have been used. However, indexes have been created on Name fields for both tb1 and tb2. I wonder why MySQL does not use the indexes to speed up the query. Is there any way to make the query faster?

EXPLAIN SELECT
         tb1.Name
FROM
         tb1, tb2
WHERE
         tb1.Name = tb2.Name;
  • 3 Contributors
  • 2 Replies
  • 108 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by varmadba

Dani AI

Generated

Short summary and practical checks based on the replies from , and .

MySQL will skip an index for a join when the optimizer thinks a full scan is cheaper (small tables, low cardinality, stale stats), when an implicit conversion prevents using the index (different data types or collations), or when the column is used in a way that prevents index use (functions, expressions, or mismatched definitions). As noted, forcing an index can prove whether the index would help; as implied, FK constraints are relevant because they normally imply an index but don’t force the optimizer to use it.

Concrete troubleshooting checklist

  • Inspect index metadata and cardinality (confirm index exists and how selective it is).
  • Ensure both join columns share the exact type, length and collation (implicit conversion on strings commonly disables indexes).
  • Refresh optimizer statistics (so the planner has current row counts). Outdated stats make scans look cheaper.
  • Look for any function or expression on the Name column in the real query that would block index use.
  • Check EXPLAIN’s possible_keys, key_len and rows columns to see what the planner considered and why it chose a scan.

Practical fixes and cautions

  • If Name is low-selectivity (many duplicates), consider joining on a surrogate integer key instead.
  • Create or adjust an index with the exact column definition the optimizer sees (same charset/collation).
  • Use index hints only to test behavior; do not blindly force hints in production because they can regress performance as data changes.
  • If needed, try rewriting the query form (different join order, EXISTS pattern, or a covering index) and validate with EXPLAIN.

For reference on how MySQL chooses and reports index usage, see the official docs: and EXPLAIN.

Recommended Answers

All 2 Replies

Member Avatar for dickersonka

are you using a foreign key on the name column?

Member Avatar for varmadba

See if you force mysql to use index

EXPLAIN SELECT
         tb1.Name
FROM
         tb1, tb2 force index(t1.name)
WHERE
         tb1.Name = tb2.Name;

and also check it name column is of same data type in both the tables

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.