MySQL Forums
Forum List  »  InnoDB

Re: Help on Query with DATE BETWEEN not performant
Posted by: Peter Brawley
Date: February 06, 2020 10:57PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help on Query with DATE BETWEEN not performant
251
February 06, 2020 10:57PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.