MySQL View Optimization: Fixing Slow Query Caused by Character Set Conversion

Background : A developer reported a view query that scans 9,000 rows in about 10 seconds, prompting an investigation.

Problem SQL : The simple query selects from the view view_dataquality_analysis with two WHERE conditions and a LIMIT of 20 rows.

SELECT *
FROM view_dataquality_analysis
WHERE modelguid = '710adae5-1900-4207-9864-d53ee3a81923'
  AND configurationguid = '6845d000-cda4-43ea-9fd3-9f9f1f22f95d' limit 20;

Running this on the development database shows an execution time of about 8 seconds.

Execution Plan : The plan shows the join order t → r → b, with the t and r tables using primary key indexes, but the b table performs a full table scan.

View Definition : The view view_dataquality_analysis is defined as a LEFT JOIN of three tables ( dataquality_taskconfigurationhistory t, dataquality_rule r, and metadata_tablebasicinfo b). Notably, the join condition between b and r includes a character‑set conversion:

(convert(`b`.`TableGuid` using utf8mb4) = `r`.`Tableguid`)

This conversion forces MySQL to treat the join as a full scan on b because the character sets differ.

Table Definitions :

CREATE TABLE `metadata_tablebasicinfo` (
  `TableGuid` varchar(50) NOT NULL,
  `SqlTableName` varchar(50) DEFAULT NULL,
  ...
  PRIMARY KEY (`TableGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dataquality_rule` (
  `RuleGuid` varchar(50) NOT NULL,
  `ModelGuid` varchar(50) DEFAULT NULL,
  ...
  PRIMARY KEY (`RuleGuid`),
  KEY `idx_top` (`RuleGuid`,`Tableguid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dataquality_taskconfigurationhistory` (
  `RowGuid` varchar(50) NOT NULL,
  `ModelGuid` varchar(50) DEFAULT NULL,
  ...
  PRIMARY KEY (`RowGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The b table uses utf8 , while t and r use utf8mb4 , confirming the suspicion that MySQL automatically adds a charset conversion when joining columns with different character sets.

Fixing the Charset : The metadata_tablebasicinfo table is altered to use utf8mb4 :

ALTER TABLE metadata_tablebasicinfo CONVERT TO CHARACTER SET utf8mb4;

After the conversion, the view still contains the charset conversion clause, so it is manually removed from the view definition and saved.

Result : Re‑executing the original query now finishes in about 0.2 seconds. The new execution plan shows the b table using its primary‑key index, confirming that the charset mismatch was the root cause of the performance issue.

Summary : Different character sets in joined columns can cause MySQL to add implicit conversion, leading to index loss and slow queries. Checking view definitions for such conversions and aligning table charsets (or removing the conversion) restores index usage and improves performance.