MySQL Forums
Forum List  »  InnoDB

Re: Help on Query with DATE BETWEEN not performant
Posted by: Peter Brawley
Date: February 09, 2020 11:48AM

> I tried to do Format=tree and Analyze but
> apparently this is not supported yet in our 5.7.19

Sorry, I forgot this isn't 8.0.

> I did run Explain extended on thew whole query:

It still needs to look at 864x54x144=6.7 million rows. On a decent machine that should be a few secs. (Virtual machines are slower.)

> This is on the subquery only:

7.7k rows

> If I force index contractADate I get this:

No. of rows cut in half.

> The five fields are like this cause before, we used full-text index
> on them, but now that we tried to do something, we saw that the
> Fulltext index was a wrong decision so we dropped the index and
> re-wrote our queries with LIKE to get better results ...

Text and LongText will never be quick searches. Have you tried moving the longtext and Text columns to a parallel earticles_txt table using the same PK? That combined with something like...

ALTER TABLE earticles ADD COLUMN pubinfo VARCHAR(600) GENERATED ALWAYS AS
( concat(left(title,100),' ',left(subtitle,100),' ',' ',left(author,100)) ) STORED;

... might speed up everything.

You'd asked earlier whether more disk use would speed things up. No but with millions of rows with multiple long varchar text and longtext columns that are frequently queried, a non-virtual machine with an SSD would. So I think would 8.0. You might want to set up such a test system.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help on Query with DATE BETWEEN not performant
190
February 09, 2020 11:48AM


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.