Re: Help on Query with DATE BETWEEN not performant

[Kristijan Marin]

Peter,

Will do that change ...

After a lot of testing we still found a slow query ...

And the thing is ... Cause when we get a list of articles we do a search for a word inside one of 5 text columns that I need to put into SELECT to be search upon ... (we hade fulltext index implemented, but was not suitable for our needs, regexp was slower then LIKE ... )

So If I have a SELECT query that has Title,subtitle,body,author,summary ... then the fetch takes 56 seconds every time ... no exception. Will see if this changes now that I executed the change of all five columns to utf8mb4

If I remove those 5 columns then it is finished in 0.5sec ... but of course, I need those 5 columns otherwise I can't search..

Another thing I found out is that my newly created indexes on new date columns are not used if I use BETWEEN ...

so if I say articleDate = '2019.01.01' index is used ... if I use "articleDAte between date1 and date2" ... only the primary index is used which is slow ...

Thanks I own you a beer or two ... at least :)
Cheers