MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize Query with multiple joins
Posted by: Angel Lacustre
Date: March 29, 2011 12:49AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize Query with multiple joins
1848
March 29, 2011 12:49AM


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.