alter table top_moc_200705_test drop index ix_top_moc_200705_date, add index ix_top_moc_200705_datemsisdn1 (date,servedmsisdn,qt_moc), add index ix_top_moc_200705_datemsisdn2 (date,servedmsisdn,qt_mtc), add index ix_top_moc_200705_datemsisdn3 (date,servedmsisdn,qt_cfw_no_vms),add index ix_top_moc_200705_datemsisdn4 (date,servedmsisdn,qt_mocSMS), add index ix_top_moc_200705_datemsisdn5 (date,servedmsisdn,qt_mtcSMS);

mysql> DESC SELECT COUNT(servedmsisdn) as qt_voice_subscribers
    -> FROM
    -> (SELECT distinct servedmsisdn
    -> FROM top_moc_200705_test
    -> WHERE date between '20070501' and '20070530' AND qt_moc>0
    -> UNION
    -> SELECT distinct servedmsisdn
    -> FROM top_moc_200705_test 
    -> WHERE date between '20070501' and '20070530' AND qt_mtc>0
    -> UNION
    -> SELECT distinct servedmsisdn
    -> FROM top_moc_200705_test
    -> WHERE date between '20070501' and '20070530' AND qt_cfw_no_vms>0) t \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: top_moc_200705_test
         type: index
possible_keys: ix_top_moc_200705_datemsisdn,ix_top_moc_200705_datemsisdn1,ix_top_moc_200705_datemsisdn2,ix_top_moc_200705_datemsisdn3,ix_top_moc_200705_datemsisdn4,ix_top_moc_200705_datemsisdn5
          key: ix_top_moc_200705_datemsisdn1
      key_len: 24
          ref: NULL
         rows: 68683612
        Extra: Using where; Using index; Using temporary
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: top_moc_200705_test
         type: range
possible_keys: ix_top_moc_200705_datemsisdn,ix_top_moc_200705_datemsisdn1,ix_top_moc_200705_datemsisdn2,ix_top_moc_200705_datemsisdn3,ix_top_moc_200705_datemsisdn4,ix_top_moc_200705_datemsisdn5
          key: ix_top_moc_200705_datemsisdn2
      key_len: 3
          ref: NULL
         rows: 55950750
        Extra: Using where; Using index; Using temporary
*************************** 4. row ***************************
           id: 4
  select_type: UNION
        table: top_moc_200705_test
         type: index
possible_keys: ix_top_moc_200705_datemsisdn,ix_top_moc_200705_datemsisdn1,ix_top_moc_200705_datemsisdn2,ix_top_moc_200705_datemsisdn3,ix_top_moc_200705_datemsisdn4,ix_top_moc_200705_datemsisdn5
          key: ix_top_moc_200705_datemsisdn3
      key_len: 24
          ref: NULL
         rows: 68683612
        Extra: Using where; Using index; Using temporary
*************************** 5. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3,4>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: 
5 rows in set (25 min 8.93 sec)