Some benchmarks:
[sql]CREATE TABLE `orders` ( `orderId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `buyerId` int(10) UNSIGNED NOT NULL, `item` int(10) UNSIGNED NOT NULL, PRIMARY KEY (`orderId`), KEY `IX_buyerId` (`buyerId`), KEY `IX_item` (`item`), KEY `IX_buyerId_item` (`buyerId`,`item`), KEY `IX_All` (`orderId`,`buyerId`,`item`)) ENGINE=MyISAM AUTO_INCREMENT=5424766 DEFAULT CHARSET=utf8;[/sql]
[sql]SELECT count(*) FROM orders;[/sql]
420016
My query (note that I had a bug in my previous query, that's why I put DISTINCT. Otherwise in case there are more than 2 identical orders, the result will be doubled):
[sql]SELECT SQL_NO_CACHE DISTINCT orders.*FROM ordersINNER JOIN orders AS repeated_orders ON orders.item = repeated_orders.item AND orders.buyerID = repeated_orders.buyerID AND orders.orderID <> repeated_orders.orderIDWHERE orders.item = 80[/sql]
46 rows fetched in 0.0020s (0.0031s)
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","orders","ref","IX_buyerId,IX_item,IX_buyerId_item","IX_item","4","const",106,"Using temporary"
1,"SIMPLE","repeated_orders","ref","IX_buyerId,IX_item,IX_buyerId_item","IX_buyerId_item","8","tt.orders.buyerId,const",1,"Using where; Distinct"
astions's query
[sql]SELECT SQL_NO_CACHE orders.*FROM ordersWHERE orders.item = 80 AND EXISTS( SELECT SQL_NO_CACHE repeated_orders.orderid FROM orders AS repeated_orders WHERE repeated_orders.buyerID = orders.buyerID AND repeated_orders.orderid != orders.orderid AND repeated_orders.item = orders.item LIMIT 1 )[/sql]
46 rows fetched in 0.0020s (0.0031s)
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","orders","ref","IX_item","IX_item","4","const",106,"Using where"
2,"DEPENDENT SUBQUERY","repeated_orders","ref","IX_buyerId,IX_item,IX_buyerId_item","IX_buyerId_item","8","tt.orders.buyerId,const",1,"Using where"
josh-Weirdan query
[sql]SELECT SQL_NO_CACHE orders.*FROM ordersWHERE buyerId IN ( SELECT SQL_NO_CACHE buyerId FROM orders WHERE item=80 GROUP BY buyerID HAVING count(*) > 1 ) AND item=80[/sql]
46 rows fetched in 0.0025s (0.1714s)
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","orders","ref","IX_item","IX_item","4","const",106,"Using where"
2,"DEPENDENT SUBQUERY","orders","ref","IX_item","IX_item","4","const",106,"Using where; Using temporary; Using filesort"
If you feel like it's not what you meant, please tell me.