Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow...

select count(*) 
from mails3 
  join questions using (question_id) 
where mails3.from_user_id = '86696' 
  and mails3.is_community_star = 1 
  and mails3.rating = 3 
  and questions.is_rated_community = 1;

the query execution plan looks simple enough but very slow

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mails3
         type: ref
possible_keys: question_id_index,emails_doublestars,favorite_emails,user_responses,owner_responses,sentmail,watchlist,read_responses,rating_date,read_and_unrated
          key: emails_doublestars
      key_len: 8
          ref: const,const,const
         rows: 2784
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: questions
         type: eq_ref
possible_keys: PRIMARY,is_rated_community_senddate_idx
          key: PRIMARY
      key_len: 5
          ref: answerology.mails3.QUESTION_ID
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)
share|improve this question
    
Add the tables' definitions so the indexes available are shown. –  ypercube Apr 8 '13 at 12:13
    
Do you have a composite index on (from_user_id, is_community_star, rating)? Try adding one. –  ypercube Apr 8 '13 at 17:34

1 Answer 1

Since you already have the necessary indexes, consider the next thing: Amazons RDS

You are most likely using the default settings. You cannot effectively tune InnoDB for multithreaded/multicore usage in smaller RDS server models.

As to server model usage of memory, here is a chart

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

Since count requires passing through many data and index pages, try using a bigger server model.

I have discussed this before:

Give it a Try !!!

share|improve this answer
    
thanks Rolando! The index is there already... I'm trying to find some server settings that might help... –  JShean Apr 8 '13 at 16:30
    
| mails3 | 1 | emails_doublestars | 1 | FROM_USER_ID | A | 54274 | NULL | NULL | | BTREE | | | | mails3 | 1 | emails_doublestars | 2 | is_community_star | A | 69807 | NULL | NULL | YES | BTREE | | | | mails3 | 1 | emails_doublestars | 3 | rating | A | 334402 | NULL | NULL | YES | BTREE | | | –  JShean Apr 8 '13 at 16:32
    
whats interesting is that it performs ok in a 5.0 environment (new environment is 5.5 RDS) –  JShean Apr 8 '13 at 16:33
    
I still cannot figure why this is slow in 5.5 on RDS. The server variables are identical as far as memory allocation goes. The explain plan is identical on old (5.0 master) and new standalone RDS server. Traffic is the same. I feel that it may be the way InnoDB is handling it. –  JShean Apr 10 '13 at 14:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.