Re: Help on Query with DATE BETWEEN not performant

[Peter Brawley]

> 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.