From: tony
Date: March 26 1999 10:10am
Subject: Strange index behaviour on double self join
List-Archive: http://lists.mysql.com/mysql/1000
Message-Id: <19990326101043.18732.qmail@crux.blackstar.co.uk>


I want to run a query which show the top selling other videos bought by 
people who have bought a specified video at our shop. 

The information required is split over 2 tables - sale, which has the
details of the individual sale including the customer ID, and date of the
sale, and sale_items, which has the details of the items in that sale.

So, to do this neatly, I use a double self join thus:

SELECT si2.itemid, COUNT(si2.itemid) as ordered
  FROM sale_items as si1, sale as s1, 
       sale_items as si2, sale as s2
 WHERE s1.saleid = si1.saleid
   AND s2.saleid = si2.saleid
   AND s1.customerid = s2.customerid
   AND si1.itemid = 7000000023298
   AND si2.itemid != 7000000023298
   AND si1.status != 99  # Don't count cancelled orders
   AND si2.status != 99  # Don't count cancelled orders
 GROUP BY si2.itemid
 ORDER BY ordered DESC, s2.when DESC
 LIMIT 5

Now, this works fine, and is fast (approx .04 secs on average)

However, if I want to not just eliminate cancelled orders (status 99),
but all orders that are still in progress, ie only include orders that 
are shipped (status 40), and change the status != 99 to
status = 40, the query takes over 20 seconds! (there are, of course,
indexes on status, itemid and saleid)

An explain on the second query gives:
+-------+--------+------------------+---------+---------+---------------+------+------------+
| table | type   | possible_keys    | key     | key_len | ref           | rows | Extra      |
+-------+--------+------------------+---------+---------+---------------+------+------------+
| si1   | ref    | saleid,itemid    | itemid  |      13 | 7000000023298 |   10 | where used |
| s1    | eq_ref | PRIMARY,personid | PRIMARY |       3 | si1.saleid    |    1 |            |
| si2   | ref    | saleid,status    | status  |       1 | ???           |   19 | where used |
| s2    | eq_ref | PRIMARY,personid | PRIMARY |       3 | si2.saleid    |    1 | where used |
+-------+--------+------------------+---------+---------+---------------+------+------------+

whereas on the first it gives:
+-------+--------+------------------+----------+---------+---------------+------+------------+
| table | type   | possible_keys    | key      | key_len | ref           | rows | Extra      |
+-------+--------+------------------+----------+---------+---------------+------+------------+
| si1   | ref    | saleid,itemid    | itemid   |      13 | 7000000023298 |   10 | where used |
| s1    | eq_ref | PRIMARY,personid | PRIMARY  |       3 | si1.saleid    |    1 |            |
| s2    | ref    | PRIMARY,personid | personid |       3 | s1.customerid |   16 |            |
| si2   | ref    | saleid           | saleid   |       3 | s2.saleid     |   18 | where used |
+-------+--------+------------------+----------+---------+---------------+------+------------+

This is on mysql-3.21.33c under Debian Linux 2.0.35 which is still
our production server database.

Under 3.22.20a, both queries take about .88 seonds, and give an explain 
of:
+-------+--------+------------------+----------+---------+---------------+-------+------------+
| table | type   | possible_keys    | key      | key_len | ref           | rows  | Extra      |
+-------+--------+------------------+----------+---------+---------------+-------+------------+
| si1   | range  | status           | status   |    NULL | NULL          | 43123 |            |
| s1    | eq_ref | PRIMARY,personid | PRIMARY  |       3 | si1.saleid    |     1 |            |
| s2    | ref    | PRIMARY,personid | personid |       3 | s1.customerid |    16 |            |
| si2   | ref    | saleid           | saleid   |       3 | s2.saleid     |    18 | where used |
+-------+--------+------------------+----------+---------+---------------+-------+------------+

Which seems to be doing it differently again ...

Would a straight_join help get it to about .04 for both queries? Or what's
going on?

Tony
-- 
-----------------------------------------------------------------------------
 Tony Bowden | tony@stripped               http://www.blackstar.co.uk/
  Black Star |    The UK's Biggest Video & DVD store * Free Postage Worldwide
-----------------------------------------------------------------------------