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