Re: Query optimization needed

[Devrishi Shandilya]

Hi Peter,

Thanks for reply, my first concern is this, in execution phase it's taking huge time in 'storing resultset in query cache', but i think it never cached, because when it comes again it again showing storing result in query cache, not served from query cache. can you explain me this behavior. I have already run flush query cache and reset query cache. my query cache setting are :

query_cache_min_res_unit = 1024
query_cache_limit = 512000
query_cache_size = 800M
query_cache_type = 1

I am using mysql 5.6 with 64 GB RAM and SSD.

Global status of query cache variables

mysql> show global status like '%qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1591 |
| Qcache_free_memory | 811144744 |
| Qcache_hits | 53953728 |
| Qcache_inserts | 189434597 |
| Qcache_lowmem_prunes | 132186957 |
| Qcache_not_cached | 19409636 |
| Qcache_queries_in_cache | 7414 |
| Qcache_total_blocks | 16881 |
+-------------------------+-----------+

Second concern is if i have turned left joins to inner joins, even after that row_examined is same. Can it be optimize further with Inner joins. suggest...

Devrishi