Public feed
#887923
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)