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
1416
February 02, 2020 06:51PM
574
February 02, 2020 07:13PM
563
February 02, 2020 10:30PM
544
February 03, 2020 04:16AM
549
February 03, 2020 08:12AM
560
February 03, 2020 10:45AM
551
February 03, 2020 11:20AM
526
February 03, 2020 12:02PM
549
February 03, 2020 12:26PM
549
February 03, 2020 04:19PM
609
February 03, 2020 06:21PM
569
February 03, 2020 08:38PM
540
February 04, 2020 01:03PM
553
February 04, 2020 01:42PM
Re: Help on Query with DATE BETWEEN not performant
759
February 04, 2020 02:48PM
526
February 04, 2020 05:26PM
549
February 04, 2020 05:27PM
510
February 06, 2020 04:21PM
585
February 06, 2020 04:31PM
548
February 06, 2020 10:57PM
538
February 09, 2020 08:46AM
530
February 09, 2020 11:48AM
516
February 09, 2020 05:04PM
509
February 04, 2020 04:12PM