Re: Help on Query with DATE BETWEEN not performant

[Kristijan Marin]

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 ...