MySQL: using NAME_CONST internally

Question

I'm using MySQL 8.0.32 I have some questions about an internal conversion, that I don't understand. In addition to this, sometimes this conversion is shown in SHOW FULL PROCESSLIST, as its execution was too long.

This is my table.

CREATE TABLE `provinces` (
 `ID` VARCHAR(2) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 `PROVINCIA` VARCHAR(45) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 PRIMARY KEY (`ID`)
 ) COLLATE='utf8mb4_swedish_ci' ENGINE=InnoDB;

I have 53 rows like these

ID	PROVINCIA
01	Álava
02	Albacete

The MySQL Function is:

CREATE FUNCTION `get_provincia`( `id_provincia` VARCHAR(2) )
RETURNS varchar(45)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
 DECLARE desc_provincia VARCHAR(45) DEFAULT '-';
 SELECT PROVINCIA INTO desc_provincia
 FROM provinces
 WHERE ID = id_provincia;
 
 RETURN desc_provincia;
END

The SHOW STATUS of this function shows:

• character_set_client: utf8mb4
• collation_connection: utf8mb4_0900_ai_ci
• database_collation: latin1_swedish_ci

That function is invoked as a field in a query.

SELECT customer_id, customer_name, ..., get_provincia(SUBSTRING(zip_code,1,2))...
FROM ...

When I'm reviewing the execution with SHOW FULL PROCESSLIST, the query inside the function is shown like this:

SELECT provincia INTO desc_provincia
FROM provincias
WHERE id = NAME_CONST('id_provincia',_latin1'15' COLLATE 'latin1_swedish_ci')

So, I have these questions:

1. Why is the NAME_CONST forced by MySQL?
2. Does it have an impact on performance? I don't understand that very simple query shows "2" value in column "Time" of SHOW FULL PROCESSLIST command, as it takes 2 seconds.

Thanks in advance.

Answer 1

I tested your example on MySQL version 8.0.39-0ubuntu0.22.04.1.

CREATE DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE test;
CREATE TABLE `provinces` (
 `ID` VARCHAR(2) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 `PROVINCIA` VARCHAR(45) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 `zip_code` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 PRIMARY KEY (`ID`)
) COLLATE='utf8mb4_swedish_ci' ENGINE=InnoDB;
INSERT INTO provinces VALUES ('01', 'Álava', '01000'), ('02', 'Albacete', '02000'),
('03', 'Alicante', '03000'),('04', 'Almería', '04000'),('05', 'Ávila', '05000'),('06', 'Badajoz', '06000'),
('07', 'Balears', '07000'), ('08', 'Barcelona', '08000'),('09', 'Burgos', '09000'),('10', 'Cáceres', '10000'),
('11', 'Cádiz', '11000'),('12', 'Castellón', '12000'),('13', 'Ciudad Real', '13000'),('14', 'Córdoba', '14000'),
('15', 'A Coruña', '15000'),('16', 'Cuenca', '16000'),('17', 'Girona', '17000'),('18', 'Granada', '18000'),
('19', 'Guadalajara', '19000'),('20', 'Guipúzcoa', '20000'),('21', 'Huelva', '21000'),('22', 'Huesca', '22000'),
('23', 'Jaén', '23000'),('24', 'León', '24000'),('25', 'Lleida', '25000'),('26', 'La Rioja', '26000'),('27', 'Lugo', '27000'),
('28', 'Madrid', '28000'),('29', 'Málaga', '29000'),('30', 'Murcia', '30000'),('31', 'Navarra', '31000'),
('32', 'Ourense', '32000'),('33', 'Asturias', '33000'),('34', 'Palencia', '34000'),('35', 'Las Palmas', '35000'),
('36', 'Pontevedra', '36000'),('37', 'Salamanca', '37000'),('38', 'Santa Cruz de Tenerife', '38000'),('39', 'Cantabria', '39000'),
('40', 'Segovia', '40000'),('41', 'Sevilla', '41000'),('42', 'Soria', '42000'),('43', 'Tarragona', '43000'),
('44', 'Teruel', '44000'),('45', 'Toledo', '45000'),('46', 'Valencia', '46000'),('47', 'Valladolid', '47000'),
('48', 'Bizkaia', '48000'),('49', 'Zamora', '49000'),('50', 'Zaragoza', '50000'),('51', 'Ceuta', '51000'),('52', 'Melilla', '52000');

I created the following function

DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `get_provincia`( `id_provincia` VARCHAR(2) )
RETURNS VARCHAR(45) CHARSET latin1
READS SQL DATA
BEGIN
 DECLARE desc_provincia VARCHAR(45) DEFAULT '-';
 SELECT PROVINCIA INTO desc_provincia
 FROM provinces
 WHERE ID = id_provincia;
 RETURN desc_provincia;
END //
DELIMITER ;

A simple bash script to catch the SHOW FULL PROCESSLIST output

#!/bin/bash
end=$((SECONDS+60)) # Run for approximately 1 minute
while [ $SECONDS -lt $end ]; do
 mysql --user=root --password='zzzzzzzzzzzzzzzz' -e "SHOW FULL PROCESSLIST;" \
 >> /home/e.basha/slow_query_3_sec.txt
 usleep 100 # Sleep for 0.0001 seconds (100 microseconds)
done

Running the query

SELECT ID,PROVINCIA,get_provincia(SUBSTRING(zip_code,1,2)) FROM provinces; 

produced

SELECT PROVINCIA INTO desc_provincia\n FROM provinces\n WHERE ID = NAME_CONST('id_provincia',_latin1'08' COLLATE 'latin1_swedish_ci');

---

Why is the NAME_CONST forced by MySQL?

Because the collation mismatch between provinces table which uses utf8mb4_swedish_ci and the query parameters which are using latin1_swedish_ci due to the database collation setting latin1_swedish_ci.

Does it have an impact on performance? I don't understand that very simple query shows "2" value in column "Time" of SHOW FULL PROCESSLIST command, as it takes 2 seconds.

Usually every collation conversions impact performance , we see that on JOINS etc.

---

What I suggest ?

Modify the function parameter to use utf8mb4_swedish_ci and always use the same collation if possible.

DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `get_provincia`( `id_provincia` VARCHAR(2) CHARSET utf8mb4 COLLATE utf8mb4_swedish_ci)
RETURNS VARCHAR(45) CHARSET utf8mb4
READS SQL DATA
BEGIN
 DECLARE desc_provincia VARCHAR(45) DEFAULT '-';
 SELECT PROVINCIA INTO desc_provincia
 FROM provinces
 WHERE ID = id_provincia;
 RETURN desc_provincia;
END //
DELIMITER ;

After running the same SELECT query as above , the SHOW FULL PROCESSLIST gives

21139379 root localhost test Query 0 closing tables SELECT ID,PROVINCIA,get_provincia(SUBSTRING(zip_code,1,2)) FROM provinces

• yaki_nuka: thank for your tips and your support. I'll give it a try, but it seems that's the solution. Thank you.
• Rick James: Or drop the FUNCTION, so SET NAMES, then recreate the FUNCTION.