1 Re the query...
SELECT
A.* ,E.fk_media,E.nrscanpages,E.dateOfArticle,E.createdate,
E.ID,E.title,E.body,E.subtitle,E.author,E.summary
FROM (
SELECT DISTINCT CA.*
FROM econtracts_articles AS CA
WHERE CA.fk_econtract IN (
SELECT fk_contract
FROM ecust_user_contract
WHERE fk_cust_user='ukommiha'
AND CA.articledate BETWEEN '2018.01.01' AND '2020.02.02'
) as A
JOIN earticles AS E ON A.fk_earticle = E.ID
WHERE (UPPER(CONCAT_WS(title,' ',subtitle,' ',body,' ',author,' ',summary)) LIKE '%drzava%' )
ORDER BY E.dateOfArticle ASC LIMIT 0,10
If I understand you correctly, you've benchmarked the inner query and found its performance acceptable.
Re the outer query, function calls and LIKE '%...%' in the Where clause prevent index use for the clause, so let's see the results of ...
Explain
Explain Format=Tree
Explain Analyze
... (i) on the entire query, and (ii) on the query without its outer Where clause.
And re the Where clause ...
(i) Upper() is redundant, and with all those spaces passed, just Concat() will do,
(ii) if you often have to scope on a concatenation of five(!) string columns, the database design needs work. Consider a derived column that precomputes that concatenation, and/or normalisation of that wedge of information to a key.
2 Re conversion to utf6mb4
1.5 days to Alter a few million rows is absurd. Something in your system is wackydoodle.
Two MySQL tricks to speed up Alter Table ... (i) drop all indexes first, then Alter Table, then recreate needed indeses, (ii) this can be faster than Alter Table..
Create Table newtbl
Select
...,
CONVERT( BINARY(CONVERT(col USING UTF8) ) USING utf8mb4),
...;
... then rename new and old tables. Test first, charsets are tricky.