gistfile1.txt

before index on opens
adrocket.opens| 9.58M  | 0.89G | 1.97G | 2.85G      |    2.21 

SELECT count(*) AS count_all FROM opens USE INDEX (IX_OPEN_OPENED) WHERE (group_id = '9' and opened between '2009-03-16 07:00:00' and '2009-03-17 07:00:00' and ad_id is not null and reason_id = 13);
+-----------+
| count_all |
+-----------+
|      1358 |
+-----------+
1 row in set (2.79 sec)

 SELECT count(*) AS count_all FROM opens WHERE (group_id = '9' and opened between '2009-03-16 07:00:00' and '2009-03-17 07:00:00' and ad_id is not null and reason_id = 13);
+-----------+
| count_all |
+-----------+
|      1358 |
+-----------+
1 row in set (34.25 sec)


create index ix_open_group_opened_ad_reason on opens (group_id, opened, ad_id, reason_id);
Query OK, 9531758 rows affected (14 min 42.68 sec)
Records: 9531758  Duplicates: 0  Warnings: 0


 SELECT count(*) AS count_all FROM opens WHERE (group_id = '9' and opened between '2009-03-16 07:00:00' and '2009-03-17 07:00:00' and ad_id is not null and reason_id = 13);
+-----------+
| count_all |
+-----------+
|      1358 |
+-----------+
1 row in set (0.43 sec)


adrocket.opens  | 9.53M  | 0.89G | 2.68G | 3.57G      |    3.01

===========================

select count(*) as CNT from opens where ad_id in (6681516,6681512) and age_group_id = 6 and gender = 'f';
+-------+
| CNT   |
+-------+
| 11890 |
+-------+
1 row in set (17.53 sec)

create index ix_open_ad_age_gender on opens (ad_id, age_group_id, gender);
Query OK, 9531762 rows affected (30 min 24.32 sec)
Records: 9531762  Duplicates: 0  Warnings: 0

select count(*) as CNT from opens where ad_id in (6681516,6681512) and age_group_id = 6 and gender = 'f';
+-------+
| CNT   |
+-------+
| 11890 |
+-------+
1 row in set (0.03 sec)

create index ix_open_ad_age_gender_group on opens (ad_id, age_group_id, gender, group_id);
Query OK, 9531762 rows affected (18 min 48.61 sec)
Records: 9531762  Duplicates: 0  Warnings: 0