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