In version 10.5.16 my code in the bottom needs for 800 records in tmp_tartikel_live 1-2 seconds.
In version 10.5.18 my code needs for 800 records in tmp_tartikel_live 20-30 seconds.
In version 10.5.16 my code needs for 26000 records in tmp_tartikel_live 5-6 minutes. It is ok.
In version 10.5.18 my code needs for 26000 records in tmp_tartikel_live more than 2 hours and
was terminating by the system and was not ready.
The execution-time glows much more than linear.
The calling script is internal for maintenance-work at night.
The purpose of this code is to build our search-tables in MySQL (concordance).
The code must work for strings with special characters in the coding-systems html (è), ansi and utf-8.
In german and france we have the special characters:
ß
ü
ö
äàá
èé
and so on.
I have found other users with performance-problems in MariaDB version 10.5.18, and they also use CONCAT:
https://dba.stackexchange.com/questions/284073/query-performance-drop-after-upgrade-to-mariadb-10-5-group-concat-join
My code is the following:
/*#JODY_20221010
Die Datenbank muss fuer diesen Code auf Kollektion=latin1_swedish_ci eingestellt sein.
ALTER DATABASE d03a5b1c COLLATE latin1_swedish_ci;
Das CHARACTER SET scheint nicht so wichtig zu sein.
*/
/*
#JODY_20211228 (ACHTUNG): Diese Datei muss ASCII-Codiert sein (nicht UTF8)
Funktion zum Loeschen von HTML-Tags. Kann nur vom browser aus installiert werden.
* /
DROP PROCEDURE IF EXISTS mysql_log;*/
DROP FUNCTION IF EXISTS mysql_striptags;
DROP FUNCTION IF EXISTS mysql_replace_utf8_sonderzeichen;
DROP FUNCTION IF EXISTS mysql_replace_single_char;
DROP FUNCTION IF EXISTS mysql_replace_char;
DROP FUNCTION IF EXISTS mysql_replace_umlaute;
/*DROP FUNCTION IF EXISTS mysql_delete_fuellwoerter;*/
DELIMITER $$
/*CREATE PROCEDURE mysql_log(strText VARCHAR(255))
BEGIN
INSERT INTO debugout(cText) VALUES (strText);
END$$*/
CREATE FUNCTION mysql_striptags(strParam TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE str TEXT;
DECLARE posOpen INT;
DECLARE posClose INT;
SET str = strParam;
loop1: LOOP
SET posOpen = LOCATE("<", str);
SET posClose = LOCATE(">", str, posOpen);
/*#JODY_20211228 Wenn posOpen 0 ist, dann ist posClose auch immer 0.*/
IF posClose = 0 THEN
LEAVE loop1;
END IF;
SET str = CONCAT(LEFT(str, posOpen - 1), " ", MID(str, posClose + 1));
END LOOP loop1;
RETURN str;
END$$
/*#JODY_20220201:
Die Moeglichkeit zur URL-Erzeugung mit UTF8-Sonderzeichen besteht darin, dass ein Anwender Strings
aus einem Eingabefeld in die Zwischenablage kopiert und dann im URL-Feld des Browsers einfuegt.
Dann stehen in der URL z.B. fuer
ä CONCAT(CHAR(195), CHAR(164))
Das Problem ist also die Eingabe von Sonderzeichen im URL-Feld des Browsers.*/
CREATE FUNCTION mysql_replace_utf8_sonderzeichen(strParam TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE str TEXT;
DECLARE nPos INT;
DECLARE nOrd INT;
DECLARE strChar VARCHAR(2);
SET str = strParam;
SET nPos = 1;
loop_for: LOOP
SET nPos = LOCATE(CHAR(195), str, nPos); /*
ACHTUNG (#JODY_20220201):
Hier kommen auch Positionen auf ein einfaches a zurueck. Liegt an MySQL. Diese brauche
ich hier zwar nicht. Aber der Code kann damit umgehen. Durch das LOCATE spare ich mir aber
fuer die grosse Menge an Zeichen die Schleifendurchlaeufe.*/
IF nPos = 0 THEN
LEAVE loop_for;
END IF;
SET strChar = MID(str, nPos, 1);
SET nPos = nPos + 1;
IF ORD(strChar) != 195 THEN
ITERATE loop_for;
END IF;
SET strChar = MID(str, nPos, 1);
SET nOrd = ORD(strChar);
IF nOrd >= 132 AND nOrd <= 188 THEN
SET str = REPLACE(str, CONCAT(CHAR(195), strChar), CHAR(nOrd + 64));
SET nPos = nPos + 1;
END IF;
END LOOP;
RETURN str;
END$$
/*
ACHTUNG (#JODY_20220120):
Da strNewStr auch nur Blanks enthalten darf/kann, kann hier nicht mit CHAR(2) gearbeitet werden.
Variablen vom Typ CHAR(x) waeren bei einem Wert von z.B. " " leer (Laenge 0), da die Laenge sich
aus dem ersten Zeichen von rechts, da kein Blank ist, ergibt. Es sind gewissermassen Fortran-Strings,
die ja auch so arbeiten. VARCHAR sind dagegen C-Strings, die 0-terminiert sind.
*/
CREATE FUNCTION mysql_replace_single_char(strParam TEXT, nChr INT, strName CHAR(10), strNewStr VARCHAR(2))
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE str TEXT;
DECLARE strNewStrTmp VARCHAR(2);
DECLARE strChar195 VARCHAR(2);
DECLARE nPosEnd INT;
SET strNewStrTmp = strNewStr;
/*ACHTUNG (#JODY_20220120): Nach meinem aktuellen Kenntnisstand koennen Parameter nicht geaendert werden.*/
IF strNewStrTmp = "<" THEN
SET strNewStrTmp = LEFT(strName, 1);
END IF;
SET str = REPLACE(strParam, CONCAT("&", strName, ";"), strNewStrTmp);
IF nChr <= 255 THEN
SET str = REPLACE(str, CHAR(nChr), strNewStrTmp);
END IF;
SET str = REPLACE(str, CONCAT("&#", CONVERT(nChr, CHAR), ";"), strNewStrTmp);
RETURN str;
END$$
CREATE FUNCTION mysql_replace_char(strParam TEXT, nChrMin INT, nChrMax INT, strNamesExt VARCHAR(40), strNewStr CHAR(1))
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE str TEXT;
DECLARE nChr INT;
DECLARE strNames VARCHAR(40);
DECLARE nPosStart INT;
DECLARE nPosEnd INT;
DECLARE bUseNames TINYINT;
DECLARE strName VARCHAR(40);
DECLARE strNewStrUpper CHAR(1);
SET str = strParam;
SET nChr = nChrMin;
IF strNamesExt != "" THEN
/*#JODY_20211229
In MySQL gibt es in Stored Procedures keine Array-Unterstuetzung.
s. https://forums.mysql.com/read.php?98,406197,406216*/
SET strNames = CONCAT("grave;acute;circ;", strNamesExt);
/*#JODY_20220408 Dieser Code ist fuer die Sonderzeichen à usw. gedacht. Nicht fuer `*/
SET bUseNames = 1;
SET strNewStrUpper = UPPER(strNewStr);
SET nPosStart = 1;
ELSE
SET bUseNames = 0;
END IF;
loop_for: LOOP
IF bUseNames != 0 THEN
SET nPosEnd = LOCATE(";", strNames, nPosStart);
SET strName = MID(strNames, nPosStart, nPosEnd - nPosStart + 1);
/*#JODY_20211229 Das ; will ich mit haben.*/
SET str = REPLACE(str, CONCAT("&", strNewStr, strName), strNewStr);
SET str = REPLACE(str, CONCAT("&", strNewStrUpper, strName), strNewStr);
/*SET strNames = MID(strNames, nPos + 1);*/
SET nPosStart = nPosEnd + 1;
/*#JODY_20211229 So ersprare ich mir dieses MID.*/
END IF;
SET str = REPLACE(str, CONCAT("&#", CONVERT(nChr, CHAR), ";"), strNewStr);
SET str = REPLACE(str, CHAR(nChr), strNewStr);
/*#JODY_20211229
Diese Funktion wird nur fuer Umlaute und nur fuer die kleinen augerufen. Diese liegen zwischen
à und ü.*/
SET str = REPLACE(str, CONCAT("&#", CONVERT(nChr - 32, CHAR), ";"), strNewStr);
SET str = REPLACE(str, CHAR(nChr - 32), strNewStr);
IF nChr >= nChrMax THEN
LEAVE loop_for;
END IF;
SET nChr = nChr + 1;
END LOOP;
RETURN str;
END$$
CREATE FUNCTION mysql_replace_umlaute(strParam TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE str TEXT;
DECLARE nPos INT;
DECLARE nChr INT;
SET str = mysql_replace_utf8_sonderzeichen(strParam); /*
ACHTUNG (#JODY_20220201):
Die UTF8-Sonderzeichen muessen hier ganz oben behandelt werden. Sie werden in ASCII-Sonderzeichen
konvertiert, die ja nachfolgend auch noch wieder konvertiert werden. So kann ich das aber allgemein
gueltig fuer alle UTF8-Sonderzeichen abhandeln.*/
SET str = REPLACE(LOWER(str), "make-up", "makeup");
/*#JODY_20220315 Make-Up soll intern als ein Wort behandelt werden.*/
/*#JODY_20220120
Die vor- und nachlaufenden Blanks werden benoetigt, damit der
Wortanfaeng/-ende richtig und leicht erkannt wird.
*/
SET str = CONCAT(" ", mysql_striptags(str), " ");
SET str = mysql_replace_char(str, 224, 230, "tilde;uml;ring;elig;", "a");
SET str = mysql_replace_char(str, 232, 235, "uml;", "e");
SET str = mysql_replace_char(str, 236, 239, "uml;", "i");
SET str = mysql_replace_char(str, 242, 246, "tilde;uml;slash;", "o");
/*#JODY_20211228
So brauche ich den Ignore-Parameter in mysql_replace_char() nicht mehr.
× und ÷ sollen hier nicht beachtet werden.*/
SET str = mysql_replace_single_char(str, 216, "Oslash", "o");
SET str = mysql_replace_single_char(str, 248, "oslash", "o");
SET str = mysql_replace_char(str, 249, 252, "uml;", "u");
SET str = mysql_replace_single_char(str, 223, "szlig", "ss");
SET str = REPLACE(str, "ae", "a");
SET str = REPLACE(str, "oe", "o");
SET str = REPLACE(str, "ue", "u");
SET str = mysql_replace_single_char(str, 191, "iquest", ""); /*#JODY_20211228 françois*/
SET str = mysql_replace_single_char(str, 199, "Ccedil", "<");/*#JODY_20211228 françois*/
SET str = mysql_replace_single_char(str, 231, "ccedil", "<");/*#JODY_20211228 françois*/
SET str = mysql_replace_single_char(str, 241, "ntilde", "<");/*#JODY_20211228 porteño*/
SET str = mysql_replace_single_char(str, 215, "times", "x");
SET str = mysql_replace_single_char(str, 160, "nbsp", " ");
SET str = mysql_replace_single_char(str, 178, "sup2", "2"); /*#JODY_20211228 Hochgestellte 2 z.B. bei dem Hersteller dsquared²*/
SET str = mysql_replace_single_char(str, 179, "sup3", "3"); /*#JODY_20211228 Hochgestellte 3 z.B. "wella care³ balance clean anti"*/
SET str = mysql_replace_single_char(str, 185, "sup1", ""); /*#JODY_20211228 Hochgestellte 1; wird aber nicht benoetigt.*/
SET str = mysql_replace_single_char(str, 8216, "lsquo", "");/*#JODY_20211228 j‘adore*/
SET str = mysql_replace_single_char(str, 8217, "rsquo", "");/*#JODY_20211228 j’adore*/
SET str = REPLACE(str, "'", ""); /*#JODY_20211228 l'oreal*/
SET str = mysql_replace_single_char(str, 34/*"*/, "quot", "");/*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 169, "copy", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 173, "shy", ""); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 174, "reg", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 176, "deg", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 183, "middot", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 8194, "ensp", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 8218, "sbquo", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 8226, "bull", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 8230, "hellip", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 8364, "euro", "euro"); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 8482, "trade", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 8722, "minus", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = REPLACE(str, "̈", "");
/*#JODY_20211228 Beispiel: "ascorbinsaure natürliches aroma"; Bei "ä" wird ein ä angezeigt.*/
SET str = mysql_replace_single_char(str, 60, "lt", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 62, "gt", " "); /*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 171, "laquo", " ");/*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 187, "raquo", " ");/*#JODY_20211228 Ist fuer die Suche nicht relevant.*/
SET str = mysql_replace_single_char(str, 189, "frac12", "");/*#JODY_20211228 "mit ca ½ liter"*/
/*#JODY_20211228 (ACHTUNG):
Prinzipiell soll ´ durch ein Blank ersetzt werden.
Nur bei loreal ist dies auf Grund des Eigennamens nicht erwuenscht.*/
SET str = mysql_replace_single_char(str, 96, "grave", " ");/*#JODY_20220408"*/
/*SET str = REPLACE(str, "l´oreal", "loreal"); #JODY_20220408*/
SET str = mysql_replace_single_char(str, 180, "acute", " ");/*#JODY_20211228 "save´n" oder "rock´n"*/
SET str = REPLACE(str, "&", " ");
/*#JODY_20220119 Es exisztieren so viele Begriffe, die in [] eingeklammert sind. z.B. [iln48006]*/
SET str = REPLACE(str, "[", " ");
SET str = REPLACE(str, "]", " ");
SET str = REPLACE(str, "#", " ");
SET str = REPLACE(str, "*", " ");
SET str = REPLACE(str, "&", " ");
SET str = REPLACE(str, "%", " ");
SET str = REPLACE(str, "|", " ");/*#JODY_20220316*/
SET str = REPLACE(str, ".", " ");
SET str = REPLACE(str, ":", " ");
SET str = REPLACE(str, ",", " ");
SET str = REPLACE(str, ";", " ");
SET str = REPLACE(str, "?", " ");
SET str = REPLACE(str, "!", " ");
SET str = REPLACE(str, "/", " ");
SET str = REPLACE(str, "\\", " ");
/*#JODY_20220120 Macht mir z.B. beim Suchbegriff "wax\" die SQL-Anweisung kaputt.*/
SET str = REPLACE(str, "-", " ");
SET str = REPLACE(str, "+", " ");
SET str = REPLACE(str, "=", " ");
SET str = REPLACE(str, "\r", " ");
SET str = REPLACE(str, "\n", " ");
SET str = REPLACE(str, "\t", " ");
SET str = REPLACE(str, "(", " ");
SET str = REPLACE(str, ")", " ");
/*
#JODY_20220121
Erstmal nicht. Die Anwender suchen meistens nach Woertern, wo ein loeschen dieser Buchstaben das
Suchergebnis eigentlich verschlechtert, da weniger Zeichenuebereinstimmungen bei Teilwoertern
gefunden werden. In Teilwoertern
#JODY_20220120 Dies soll die Menge der Wortredundanzen verringern. z.B. zugewandte/zugewandt* /
SET str = REPLACE(str, "em ", " ");
SET str = REPLACE(str, "en ", " ");
SET str = REPLACE(str, "es ", " ");
SET str = REPLACE(str, "er ", " ");
SET str = REPLACE(str, " sie ", " ");
/*#JODY_20220120
Dies ist so noetig, da "sie" durch das naechste REPLACE zu "si" wird und eine Gleichbehandlung
von "sie" und "si" wegen der vielen Suchen nach "si" nicht erwunescht ist. Ausserdem macht die
Suche nach "sie" eigentlich auch keinen Sinn.* /
SET str = REPLACE(str, "e ", " ");
SET str = REPLACE(str, "ig ", " ");*/
/*#JODY_20211229 Bisher koennen durchaus mehrere Leerzeichen hintereinander sein. Diese jetzt zu einem zusammenstreichen.*/
loop1: LOOP
SET nPos = LOCATE(" ", str);
IF nPos = 0 THEN
LEAVE loop1;
END IF;
SET str = REPLACE(str, " ", " ");
END LOOP loop1;
SET str = TRIM(str);
/*#JODY_20220120
Die vor- und nachlaufenden Blanks wieder loeschen. Wurde nur eingefuegt,
damit der Wortanfaeng/-ende richtig und leicht erkannt wird.
*/
RETURN str;
END$$
DELIMITER ;
CREATE TABLE tmp_tartikel_live (
kArtikel INT UNSIGNED,
cArtNr VARCHAR(31),
cSeo VARCHAR(255),
cSeoU TEXT,
cBeschreibung TEXT,
cBeschreibungU TEXT,
cName VARCHAR(255),
cSuchbegriffe TEXT,
cKurzBeschreibung TEXT,
kurztyp TEXT,
kurzserie TEXT,
kurzname TEXT
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
SELECT
tmp_tartikel_live.kArtikel,
tmp_tartikel_live.cArtNr,
tmp_tartikel_live.cSeo,
tmp_tartikel_live.cBeschreibung,
CONCAT(" ", mysql_replace_umlaute(tmp_tartikel_live.cName), " ") AS cNameU,
CONCAT(" ", mysql_replace_umlaute(tmp_tartikel_live.cSuchbegriffe), " ") AS cSuchbegriffeU,
CONCAT(" ", mysql_replace_umlaute(tmp_tartikel_live.cKurzBeschreibung), " ") AS cKurzBeschreibungU,
CONCAT(" ", mysql_replace_umlaute(tmp_tartikel_live.cBeschreibung), " ") AS cBeschreibungU,
CONCAT(" ", mysql_replace_umlaute(tmp_tartikel_live.cSeo), " ") AS cSeoU,
CONCAT(" ", mysql_replace_umlaute(tmp_tartikel_live.kurztyp), " ") AS kurztypU,
CONCAT(" ", mysql_replace_umlaute(tmp_tartikel_live.kurzserie), " ") AS kurzserieU,
CONCAT(" ", mysql_replace_umlaute(tmp_tartikel_live.kurzname), " ") AS kurznameU
FROM tmp_tartikel_live
This code prepare article-text for search in. Is this problem known or is it new.
I hope, I do it right in this way.
Johannes Ody
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
