select count(*) in mysql 5.5 innodb-- rewrite advice?

Question

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)

score 2 · Answer 1 · edited Apr 13 '17 at 12:42

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:

Aug 02, 2012 : Local database vs Amazon RDS
Feb 26, 2013 : MySQL optimization

Give it a Try !!!

select count(*) in mysql 5.5 innodb-- rewrite advice?

1 Answers1