Re: Help on Query with DATE BETWEEN not performant
> 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.
Subject
Views
Written By
Posted
1005
February 02, 2020 06:51PM
301
February 02, 2020 07:13PM
307
February 02, 2020 10:30PM
303
February 03, 2020 04:16AM
319
February 03, 2020 08:12AM
303
February 03, 2020 10:45AM
302
February 03, 2020 11:20AM
275
February 03, 2020 12:02PM
306
February 03, 2020 12:26PM
291
February 03, 2020 04:19PM
279
February 03, 2020 06:21PM
305
February 03, 2020 08:38PM
298
February 04, 2020 01:03PM
304
February 04, 2020 01:42PM
291
February 04, 2020 02:48PM
289
February 04, 2020 05:26PM
313
February 04, 2020 05:27PM
286
February 06, 2020 04:21PM
293
February 06, 2020 04:31PM
290
February 06, 2020 10:57PM
290
February 09, 2020 08:46AM
Re: Help on Query with DATE BETWEEN not performant
287
February 09, 2020 11:48AM
293
February 09, 2020 05:04PM
283
February 04, 2020 04:12PM