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
1380
February 02, 2020 06:51PM
560
February 02, 2020 07:13PM
550
February 02, 2020 10:30PM
532
February 03, 2020 04:16AM
538
February 03, 2020 08:12AM
548
February 03, 2020 10:45AM
538
February 03, 2020 11:20AM
514
February 03, 2020 12:02PM
534
February 03, 2020 12:26PM
532
February 03, 2020 04:19PM
580
February 03, 2020 06:21PM
553
February 03, 2020 08:38PM
525
February 04, 2020 01:03PM
539
February 04, 2020 01:42PM
Re: Help on Query with DATE BETWEEN not performant
696
February 04, 2020 02:48PM
516
February 04, 2020 05:26PM
538
February 04, 2020 05:27PM
495
February 06, 2020 04:21PM
571
February 06, 2020 04:31PM
537
February 06, 2020 10:57PM
520
February 09, 2020 08:46AM
518
February 09, 2020 11:48AM
497
February 09, 2020 05:04PM
490
February 04, 2020 04:12PM