# Query_time: 1024  Lock_time: 0  Rows_sent: 8018  Rows_examined: 1024810
use store_wtso;
select pl.*, p.affiliate as affiliate
                        from purchaseledger pl, purchase p
                        where pl.purchase_id=p.id
                        and p.liveon >= '2008-01-25 00:00:00' and p.liveon <= '2008-02-01 23:59:59'
                        and pl.status='a'
                        and p.status<>'d'
                        order by created desc;



******
EXPLAIN $QUERY_FROM_ABOVE
******

+----+-------------+-------+--------+-----------------------+---------+---------+---------------------------+--------+-----------------------------+
| id | select_type | table | type   | possible_keys         | key     | key_len | ref                          | rows   | Extra                       |
+----+-------------+-------+--------+-----------------------+---------+---------+---------------------------+--------+-----------------------------+
|  1 | SIMPLE      | pl    | ALL    | purch_id              | NULL    |    NULL | NULL                      | 342968 | Using where; Using filesort |

|  1 | SIMPLE      | p     | eq_ref | PRIMARY,status_liveon | PRIMARY |       4 | store_wtso.pl.purchase_id |      1 | Using where                 |
+----+-------------+-------+--------+-----------------------+---------+---------+---------------------------+--------+-----------------------------+
2 rows in set (0.21 sec)



mysql> show index from purchase;                                                           +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| purchase |          0 | PRIMARY       |            1 | id          | A         |       38859 |     NULL | NULL   |      | BTREE      |         |
| purchase |          1 | status_liveon |            1 | status      | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |
| purchase |          1 | status_liveon |            2 | liveon      | A         |       38859 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)