Re: Help on Query with DATE BETWEEN not performant
The query cache remains disabled??
[] Yes I even set it into the config file in case of a reset
Are there many of null or invalid or 0000-00-00 dates? -
[] no no nulls
One at a time, what's performance with ...
- filter on different and/or smaller earticles dateofarticle ranges?
[] I did tests on : one day,one week, one month and one year. Starting from today ...results were linear from 5-10 seconds to 160+sec
- no date range
[] all queries are always run with date range
- after you drop and recreate primary and dateofarticle indexes?
[] I did Optimize table on all tables, and I think the result from optimize says that there is no optimize available so recreate is done ...and I read somewhere this should recreate indexes and even table ?
- after you convert all utf8 cols to utf8mb4?
[] I did not do that ...
Peter, cause I was almost desperate with how all this was working I had to do something ... I did not fix this problem , which will stay a mystery ... but I did solve my performance with :
- adding datetime column on econtract_article table
- adding trigger on this table that populates value from earticles
- rewrite queries to use this datetime and not the one on earticles
Now everything is working much much faster .... or should I say
very acceptable :D
My times are now everywhere in millis to a few seconds ... Like searching for a totally new customer for a year took like 16 sec max ... and the return was around 12000 records ... once run , the second time in millis of course ...
I know this would not be possible and is the result of your help with many hints and a small part of my rewrite.
For that I would like to really say :
Thank You Peter. Thank you for the help. Much appreciated.
P.s.: Despite I didn't solve the "root" of the problem, it would be really good to know what the heck was/is the problem and why even the explain plans showed ?wrong? plan statistics ...
Subject
Views
Written By
Posted
1404
February 02, 2020 06:51PM
570
February 02, 2020 07:13PM
561
February 02, 2020 10:30PM
542
February 03, 2020 04:16AM
547
February 03, 2020 08:12AM
557
February 03, 2020 10:45AM
546
February 03, 2020 11:20AM
524
February 03, 2020 12:02PM
546
February 03, 2020 12:26PM
544
February 03, 2020 04:19PM
Re: Help on Query with DATE BETWEEN not performant
605
February 03, 2020 06:21PM
565
February 03, 2020 08:38PM
536
February 04, 2020 01:03PM
551
February 04, 2020 01:42PM
739
February 04, 2020 02:48PM
523
February 04, 2020 05:26PM
546
February 04, 2020 05:27PM
506
February 06, 2020 04:21PM
581
February 06, 2020 04:31PM
545
February 06, 2020 10:57PM
533
February 09, 2020 08:46AM
528
February 09, 2020 11:48AM
514
February 09, 2020 05:04PM
499
February 04, 2020 04:12PM