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,
Kris