MySQL Forums
Forum List  »  InnoDB

Re: Help on Query with DATE BETWEEN not performant
Posted by: Kristijan Marin
Date: February 03, 2020 04:16AM

Hi Peter,

Thank you so much for the reply.

Server is 5.7.19 on ubuntu 16.04.1 and it has 20Gb ram ... it's a virtual machine.

Key efficiency shows 97,3%, innodb buffer usage is 99,2%.

innodb_buffer_pool_chunk_size	134217728
innodb_buffer_pool_dump_at_shutdown	ON
innodb_buffer_pool_dump_now	OFF
innodb_buffer_pool_dump_pct	25
innodb_buffer_pool_filename	ib_buffer_pool
innodb_buffer_pool_instances	8
innodb_buffer_pool_load_abort	OFF
innodb_buffer_pool_load_at_startup	ON
innodb_buffer_pool_load_now	OFF
innodb_buffer_pool_size	17179869184

have_query_cache	YES
long_query_time	10.000000
query_alloc_block_size	8192
query_cache_limit	10485760
query_cache_min_res_unit	4096
query_cache_size	16777216
query_cache_type	OFF
query_cache_wlock_invalidate	OFF
query_prealloc_size	8192

The thing is that no one configured this machine at all. Last week I wanted to see why is it SO slow .. and did some investigation
to find out that the pool size was set to 125MB , so I set it to 80% and increased the instance value from 1 to 8. Didn't do anything else...

I ran your query ... the explain was more or less the same and it took it to return 6349 rows 67seconds ....

What I somehow found out as well is this ....

This inner query that I said was quick ... which was not :) ...
(SELECT E.ID,E.dateOfArticle from econtracts_articles AS CA 
     JOIN earticles AS E ON CA.fk_earticle = E.ID 
     WHERE CA.fk_econtract IN (SELECT fk_contract FROM ecust_user_contract WHERE fk_cust_user= 'mihaluka'  )

If I do only the query on econtract_articles with the IN ... it is really fast cause it returns 78710 rows in 1,9sec.
Mysql Workbench shows duration = 0.046sec ,fetch = 1.888sec for this query.

so like this :
SELECT * from econtracts_articles AS CA 
     WHERE CA.fk_econtract IN (SELECT fk_contract FROM ecust_user_contract WHERE fk_cust_user= 'mihaluka')

But when I connect the query to that main table "earticles" with the JOIN then it is super slow ...
So why would connection 40k rows to the main table through the primary index took so long? I would expect it to be fast .... probably some temporary table is used during the join and maybe this is slow or has not enough memory/space for it to efficiently do this ?

I did try to run the same query now on another customer, that had 41559 rows and that took without that earticle
join duration 0.063sec/ fetch 1.154sec ....
and then running it with a join ... took duration 0.078sec/fetch 115sec ??

You mentioned that the explain result doesn't look right for so many millions of rows ... was weird to me to, but thought maybe that's just something it's supposed to be like this ...

Thank you,

Options: ReplyQuote

Written By
Re: Help on Query with DATE BETWEEN not performant
February 03, 2020 04:16AM

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.