MySQL Forums
Forum List  »  InnoDB

Re: Help on Query with DATE BETWEEN not performant
Posted by: Kristijan Marin
Date: February 03, 2020 06:21PM

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

Options: ReplyQuote

Written By
Re: Help on Query with DATE BETWEEN not performant
February 03, 2020 06:21PM

Sorry, you can't reply to this topic. It has been closed.

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.