Re: Help on Query with DATE BETWEEN not performant

[Kristijan Marin]

I need to share this ...

Don't want to jump to the conclusion to quickly .... but ...:)

At some point, I turned the catch-all exception inside my VisualStudio Asp.NEt .project ...

what happened was that I got an unusual exception from the db adapter ...

saying something about "Row 2345 was cut in GROUP_CONCAT ..." something like that...

so I searched for all my functions where this could be called and found that I have a method actually 2 methods that use that GROUP_CONCAT...

googling around I found out that it could be something about the "group_concat_max_len" attribute being to small ...

So I ran "SET global group_concat_max_len=15000;" ...

Now the same query that took before 56sec running it directly from Mysql workbench as described in my previous mail ... was done in 2.5sec :)

I just wanted to let you know about this one :) .... I would not guess that, cause I didn't get any normal exception just timeout on my project side but in Workbench it just took as I said 56sec ... it didn't stop on error ?Hmm ,,, why ?? Why did the query continue, despite there was an error when processing individual rows .... that probably made this delay ...some sort of a silent exception

If you still have any hint on how to create an index that when having BETWEEN dates .... would be great.

Thanks,
Cheers,
Kris

P.S: we tried to convert the table to utf8mb4 but then I remembered that we did try this last yest and it took 1.5days but still didn't finish, so we will plan to make this over the weekend .... probably the best thing is to create another table by doing insert from select? Any quicker way? Dump?