Re: Optimize Query with multiple joins

[Angel Lacustre]

Hi Rick,

thanks for the suggestions, but those indexes didn't change the query plan. I think that index-related the query is fine. The EXPLAIN already shows Using Index.

The 1.4sec query time was registered under heavy load, with many concurrent calls on our web server. Each of Apache's processes can trigger up to 10-12 MySQL queries, from simple to more complex like the current case, tho, as you stated, our dataset is reduced. At this moment we are not using persistent connections.

I'm considering using stored procedures on this specific case, as up to 3-4 similar queries may be executed for a single call.

Here are the server values

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_alloc_block_size       | 8192      |
| query_cache_limit            | 8388608   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
| query_prealloc_size          | 8192      |
+------------------------------+-----------+

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 3886     |
| Qcache_free_memory      | 12626640 |
| Qcache_hits             | 8573058  |
| Qcache_inserts          | 3479315  |
| Qcache_lowmem_prunes    | 1161019  |
| Qcache_not_cached       | 177435   |
| Qcache_queries_in_cache | 15790    |
| Qcache_total_blocks     | 61386    |
+-------------------------+----------+

Thanks!



Edited 2 time(s). Last edit at 03/29/2011 12:54AM by Angel Lacustre.