slow mysql query need help

[baileigh]

I want to retrieve count of unread message for each phone number from the table user_message. Below mentioned query takes 2996 ms to give result which is super slow.

Extra Info of table: table contains 6.6 million rows

I have Indexed Column phone_num and m_status

    SELECT  COUNT ( * ) AS `__count`
        FROM  `user_message`
        WHERE  ( `user_message` . `phone_num` = "12312312312"
          AND  `user_message` . `m_status` = "unread" 
               ) 

Excexution Plan
{

"query_block": {

"select_id": 1,

"cost_info": {

"query_cost": "293495.86"

},

"table": {

"table_name": "user_message",

"access_type": "index_merge",

"possible_keys": [

"user_m_statu_997f64_idx",

"phone_num_7f5e39_idx"

],

"key": "intersect(user_m_statu_997f64_idx,phone_num_7f5e39_idx)",

"key_length": "402,2002",

"rows_examined_per_scan": 922210,

"rows_produced_per_join": 922210,

"filtered": "100.00",

"using_index": true,

"cost_info": {

"read_cost": "201274.86",

"eval_cost": "92221.00",

"prefix_cost": "293495.86",

"data_read_per_join": "10G"

},

"used_columns": [

"m_status",

"phone_num"

],

"attached_condition": "((db.user_message.m_status = 'unread') and (db.user_message.phone_num = '123213123'))"

}

}

}

[obi]

Indexing the columns individually means that you will need to find out all the rows that match the phone_num = "12312312312" predicate and then find out all the rows that match the m_status = "unread" predicate, and then merge the results sets together. Alternatively, it could just use one of the indexes and read the rows by index lookups and check the other column.

If neither filter is suitably selective on it's own, you will want to use a composite index:

create index user_message_number_status
       on user_message (phone_num, m_status);

For this particular query, the order of columns in the index won't matter much. But if you are looping this query for several different phone_num values, you might be better off with m_status being the lead column (although it probably won't make a huge difference).