mysql> explain SELECT pl.*, p.affiliate FROM purchase p INNER JOIN purchaseledger pl ON pl.purchase_id=p.id
    -> WHERE 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;
+----+-------------+-------+-------+-----------------------+----------+---------+-----------------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys         | key      | key_len | ref             | rows | Extra                                        |
+----+-------------+-------+-------+-----------------------+----------+---------+-----------------+------+----------------------------------------------+
|  1 | SIMPLE      | p     | range | PRIMARY,status,liveon | liveon   |       9 | NULL            | 1125 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | pl    | ref   | purch_id,status       | purch_id |       5 | store_wtso.p.id |    9 | Using where                                  |
+----+-------------+-------+-------+-----------------------+----------+---------+-----------------+------+----------------------------------------------+
2 rows in set (0.00 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         |       38884 |     NULL | NULL   |      | BTREE      |         |
| purchase |          1 | status   |            1 | status      | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |
| purchase |          1 | liveon   |            1 | liveon      | A         |       38884 |     NULL | NULL   | YES  | BTREE      |         |
| purchase |          1 | created  |            1 | created     | A         |       38884 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

mysql> show index from purchaseledger;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| purchaseledger |          0 | PRIMARY  |            1 | id          | A         |      343183 |     NULL | NULL   |      | BTREE      |         |
| purchaseledger |          1 | purch_id |            1 | purchase_id | A         |       38131 |     NULL | NULL   | YES  | BTREE      |         |
| purchaseledger |          1 | status   |            1 | status      | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |
| purchaseledger |          1 | created  |            1 | created     | A         |       85795 |     NULL | NULL   | YES  | BTREE      |         |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.01 sec)