MySQL Forums
Forum List  »  InnoDB

Re: Help on Query with DATE BETWEEN not performant
Posted by: Kristijan Marin
Date: February 04, 2020 02:48PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help on Query with DATE BETWEEN not performant
231
February 04, 2020 02:48PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.