2

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)
2
  • Add the tables' definitions so the indexes available are shown. Apr 8, 2013 at 12:13
  • Do you have a composite index on (from_user_id, is_community_star, rating)? Try adding one. Apr 8, 2013 at 17:34

1 Answer 1

2

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 !!!

4
  • thanks Rolando! The index is there already... I'm trying to find some server settings that might help...
    – JShean
    Apr 8, 2013 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, 2013 at 16:32
  • whats interesting is that it performs ok in a 5.0 environment (new environment is 5.5 RDS)
    – JShean
    Apr 8, 2013 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, 2013 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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