EXPLAIN SELECT pl.*, p.affiliate FROM purchaseledger pl INNER JOIN purchase p 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      | pl    | ALL    | purch_id,purchase_id  | NULL    |    NULL | NULL                      | 257312 | Using where; Using filesort |

|  1 | SIMPLE      | p     | eq_ref | PRIMARY,status_liveon | PRIMARY |       4 | store_wtso.pl.purchase_id |      1 | Using where                 |
+----+-------------+-------+--------+-----------------------+---------+---------+---------------------------+--------+-----------------------------+



mysql> describe purchaseledger;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(15)     |      | PRI | NULL    | auto_increment |
| user_id     | int(15)     | YES  |     | NULL    |                |
| purchase_id | int(15)     | YES  | MUL | NULL    |                |
| parent_id   | int(15)     | YES  |     | NULL    |                |
| product_id  | int(15)     | YES  |     | NULL    |                |
| fk_id       | int(15)     | YES  |     | 0       |                |
| status      | char(1)     | YES  |     | NULL    |                |
| type        | varchar(8)  | YES  |     | NULL    |                |
| name        | tinytext    | YES  |     | NULL    |                |
| descr       | tinytext    | YES  |     | NULL    |                |
| amount      | double(8,2) | YES  |     | NULL    |                |
| created     | datetime    | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

mysql> describe purchase
    -> ;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(15)      |      | PRI | NULL    | auto_increment |
| clid             | int(15)      | YES  |     | NULL    |                |
| user_id          | int(15)      | YES  |     | NULL    |                |
| hasrecurring     | char(1)      | YES  |     | n       |                |
| status           | char(1)      | YES  | MUL | NULL    |                |
| liveon           | datetime     | YES  |     | NULL    |                |
| parent_id        | int(15)      | YES  |     | -1      |                |
| public_invoiceid | tinytext     | YES  |     | NULL    |                |
| sfname           | tinytext     | YES  |     | NULL    |                |
| slname           | tinytext     | YES  |     | NULL    |                |
| scompany         | tinytext     | YES  |     | NULL    |                |
| semail           | tinytext     | YES  |     | NULL    |                |
| sstreet1         | tinytext     | YES  |     | NULL    |                |
| sstreet2         | tinytext     | YES  |     | NULL    |                |
| scity            | tinytext     | YES  |     | NULL    |                |
| sstate           | tinytext     | YES  |     | NULL    |                |
| szip             | tinytext     | YES  |     | NULL    |                |
| scountry         | tinytext     | YES  |     | NULL    |                |
| sphone           | tinytext     | YES  |     | NULL    |                |
| sphoneext        | varchar(10)  | YES  |     | NULL    |                |
| bfname           | tinytext     | YES  |     | NULL    |                |
| blname           | tinytext     | YES  |     | NULL    |                |
| bemail           | tinytext     | YES  |     | NULL    |                |
| bcompany         | tinytext     | YES  |     | NULL    |                |
| bstreet1         | tinytext     | YES  |     | NULL    |                |
| bstreet2         | tinytext     | YES  |     | NULL    |                |
| bcity            | tinytext     | YES  |     | NULL    |                |
| bstate           | tinytext     | YES  |     | NULL    |                |
| bzip             | tinytext     | YES  |     | NULL    |                |
| bcountry         | tinytext     | YES  |     | NULL    |                |
| bphone           | tinytext     | YES  |     | NULL    |                |
| bphoneext        | varchar(10)  | YES  |     | NULL    |                |
| ccard            | tinytext     | YES  |     | NULL    |                |
| ccnum            | varchar(26)  | YES  |     | NULL    |                |
| ccexp            | date         | YES  |     | NULL    |                |
| authstatus       | char(1)      | YES  |     | NULL    |                |
| authcode         | tinytext     | YES  |     | NULL    |                |
| authtransid      | tinytext     | YES  |     | NULL    |                |
| authextra1       | tinytext     | YES  |     | NULL    |                |
| authamount       | double(8,2)  | YES  |     | NULL    |                |
| shipcode         | varchar(7)   | YES  |     | NULL    |                |
| shipper          | tinytext     | YES  |     | NULL    |                |
| shippingmethod   | tinytext     | YES  |     | NULL    |                |
| public_subtotal  | double(10,2) | YES  |     | NULL    |                |
| public_tax       | double(10,2) | YES  |     | NULL    |                |
| public_handling  | double(10,2) | YES  |     | NULL    |                |
| public_shipping  | double(10,2) | YES  |     | NULL    |                |
| public_total     | double(10,2) | YES  |     | NULL    |                |
| comment          | text         | YES  |     | NULL    |                |
| affiliate        | tinytext     | YES  |     | NULL    |                |
| created          | datetime     | YES  |     | NULL    |                |
| session_id       | varchar(42)  | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
52 rows in set (0.00 sec)