Re: Help on Query with DATE BETWEEN not performant
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
Subject
Views
Written By
Posted
1854
February 02, 2020 06:51PM
736
February 02, 2020 07:13PM
722
February 02, 2020 10:30PM
702
February 03, 2020 04:16AM
686
February 03, 2020 08:12AM
718
February 03, 2020 10:45AM
692
February 03, 2020 11:20AM
674
February 03, 2020 12:02PM
709
February 03, 2020 12:26PM
715
February 03, 2020 04:19PM
1081
February 03, 2020 06:21PM
721
February 03, 2020 08:38PM
691
February 04, 2020 01:03PM
723
February 04, 2020 01:42PM
Re: Help on Query with DATE BETWEEN not performant
1097
February 04, 2020 02:48PM
706
February 04, 2020 05:26PM
692
February 04, 2020 05:27PM
638
February 06, 2020 04:21PM
745
February 06, 2020 04:31PM
697
February 06, 2020 10:57PM
723
February 09, 2020 08:46AM
699
February 09, 2020 11:48AM
639
February 09, 2020 05:04PM
644
February 04, 2020 04:12PM