Форум пользователей MySQL

EXPLAIN SELECT
                    s.`user_id`,
                    s.`user_subid`,
                    SUM(c.`iteration`=1) as first,
                    SUM(c.`iteration`=2) as second,
                    SUM(c.`iteration`=3) as third,
                    SUM(c.`iteration`>3) as more,
                    SUM(c.`user_amount`) as user_amount,
                    SUM(c.`user_amount`/c.`exchrate`) as user_us_amount,
                    s.`project_id`
                FROM
                    `tb_subscriptions` s,
                    `tb_member_codes` c FORCE INDEX(`subscription_id-iter`)
                WHERE
                    s.`date` between "2011-12-16 00:00:00" and "2011-12-16 23:59:59"
                    and c.`subscription_id` = s.`ID`
                GROUP BY
                    s.`user_subid`,
                    s.`project_id`;
+----+-------------+-------+--------+--------------------------+---------+---------+--------------------------------------+---------+-----------------------------------+
| id | select_type | table | type   | possible_keys            | key     | key_len | ref                                  | rows    | Extra                             |
+----+-------------+-------+--------+--------------------------+---------+---------+--------------------------------------+---------+-----------------------------------+
|  1 | SIMPLE      | c     | ALL    | subscription_id-iter     | NULL    | NULL    | NULL                                 | 6179122 | Using temporary; Using filesort   |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY,date,for_rebills | PRIMARY | 4       | db_handler.c.subscription_id |       1 | Using where with pushed condition |
+----+-------------+-------+--------+--------------------------+---------+---------+--------------------------------------+---------+-----------------------------------+
2 rows in set



EXPLAIN SELECT
                    s.`user_id`,
                    s.`user_subid`,
                    SUM(c.`iteration`=1) as first,
                    SUM(c.`iteration`=2) as second,
                    SUM(c.`iteration`=3) as third,
                    SUM(c.`iteration`>3) as more,
                    SUM(c.`user_amount`) as user_amount,
                    SUM(c.`user_amount`/c.`exchrate`) as user_us_amount,
                    s.`project_id`
                FROM
                    `tb_subscriptions` s,
                    `tb_member_codes` c USE INDEX(`subscription_id-iter`)
                WHERE
                    s.`date` between "2011-12-16 00:00:00" and "2011-12-16 23:59:59"
                    and c.`subscription_id` = s.`ID`
                GROUP BY
                    s.`user_subid`,
                    s.`project_id`;
+----+-------------+-------+--------+--------------------------+---------+---------+--------------------------------------+---------+-----------------------------------+
| id | select_type | table | type   | possible_keys            | key     | key_len | ref                                  | rows    | Extra                             |
+----+-------------+-------+--------+--------------------------+---------+---------+--------------------------------------+---------+-----------------------------------+
|  1 | SIMPLE      | c     | ALL    | subscription_id-iter     | NULL    | NULL    | NULL                                 | 6178604 | Using temporary; Using filesort   |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY,date,for_rebills | PRIMARY | 4       | db_handler.c.subscription_id |       1 | Using where with pushed condition |
+----+-------------+-------+--------+--------------------------+---------+---------+--------------------------------------+---------+-----------------------------------+
2 rows in set




EXPLAIN SELECT
                    s.`user_id`,
                    s.`user_subid`,
                    SUM(c.`iteration`=1) as first,
                    SUM(c.`iteration`=2) as second,
                    SUM(c.`iteration`=3) as third,
                    SUM(c.`iteration`>3) as more,
                    SUM(c.`user_amount`) as user_amount,
                    SUM(c.`user_amount`/c.`exchrate`) as user_us_amount,
                    s.`project_id`
                FROM
                    `tb_subscriptions` s FORCE INDEX(`date`),
                    `tb_member_codes` c
                WHERE
                    s.`date` between "2011-12-16 00:00:00" and "2011-12-16 23:59:59"
                    and c.`subscription_id` = s.`ID`
                GROUP BY
                    s.`user_subid`,
                    s.`project_id`;
+----+-------------+-------+-------+------------------------------------------------------------+-----------------+---------+-------------------------+-------+--------------------------------------------------------------------+
| id | select_type | table | type  | possible_keys                                              | key             | key_len | ref                     | rows  | Extra                                                              |
+----+-------------+-------+-------+------------------------------------------------------------+-----------------+---------+-------------------------+-------+--------------------------------------------------------------------+
|  1 | SIMPLE      | s     | range | date                                                       | date            | 8       | NULL                    | 78914 | Using where with pushed condition; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | ref   | subscription_id,subscription_id-level,subscription_id-iter | subscription_id | 4       | db_handler.s.ID |    18 | Using where                                                        |
+----+-------------+-------+-------+------------------------------------------------------------+-----------------+---------+-------------------------+-------+--------------------------------------------------------------------+
2 rows in set