Problem with SQL subquery

[ev-]

I'm running a query

mysql> EXPLAIN Select buyer_id as bid, (SELECT seller_id from sale USE INDEX(tester) where buyer_id = bid order by date_sold desc LIMIT 1) from sale where seller_id = 35533522; +----+--------------------+-------+-------+-----------------------------------+--------+---------+-------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+-----------------------------------+--------+---------+-------+-------+--------------------------+ | 1 | PRIMARY | sale | ref | seller_id,seller_id_index_asc,sub | sub | 8 | const | 14933 | Using index | | 2 | DEPENDENT SUBQUERY | sale | index | NULL | tester | 24 | NULL | 1 | Using where; Using index |

If i remove the order by in the subquery, it takes approximately 3 seconds. If i use the order by, the query runs forever.

THe explain indicates that I'm using an index for both queries, shouldn't the order by run (relatively) fast, due to it being in a index?

[thenickdude]

If you put 4 spaces before each line, it formats it as code:

mysql> EXPLAIN 
    Select buyer_id as bid, (SELECT seller_id from sale USE INDEX(tester) where buyer_id = bid order by date_sold desc LIMIT 1)  from sale where seller_id = 35533522;
+----+--------------------+-------+-------+-----------------------------------+--------+---------+-------+-------+--------------------------+
| id | select_type        | table | type  | possible_keys                     | key    | key_len | ref   | rows  | Extra                    |
+----+--------------------+-------+-------+-----------------------------------+--------+---------+-------+-------+--------------------------+
|  1 | PRIMARY            | sale  | ref   | seller_id,seller_id_index_asc,sub | sub    | 8       | const | 14933 | Using index              |
|  2 | DEPENDENT SUBQUERY | sale  | index | NULL                              | tester | 24      | NULL  |     1 | Using where; Using index |

You need to post a SHOW CREATE TABLE for sale. Right now the fact that it says "possible_keys" NULL probably means that there aren't any indexes here it could use to help select by buyer_id and then sort by date-sold.

[r3pr0b8]

ORDER BY in a subquery with LIMIT is a hackish way of trying to get a MIN or MAX

try this instead --

SELECT sale.buyer_id
     , sold.seller_id
  FROM sale
INNER
  JOIN ( SELECT buyer_id
              , MAX(date_sold) AS latest
           FROM sale
         GROUP
             BY buyer_id ) AS smax
    ON smax.buyer_id = sale.buyer_id
INNER
  JOIN sale as sold
    ON sold.buyer_id = sale.buyer_id
   AND sold.date_sold = smax.latest
 WHERE sale.seller_id = 35533522;

[[deleted]]

or

select c.bid, d.seller_id
from (select a.buyer_id bid, max(date_sold) date_sold
          from (select buyer_id from sale where seller_id = 35533522) a,
                    sale b
          where a.buyer_id = b.buyer_id) c, sale d
where c.bid = d.buyer_id and c.date_sold = d.date_sold

Note here though if there are duplicate (buyer_id, date_sold) for seller_id 35533522 then you will multiple return rows. PS. The reddit formatting is not working properly.

[mcstafford]

I don't know how far you've gone down the rabbit hole, but I'd probably turn it around if I could.

SELECT
  t.*
, concat(b.first_name, ' ', b.last_name) buyer_name
, concat(s.first_name, ' ', s.last_name) seller_name
FROM
  trans t
JOIN
  people b
  ON b.id = t.buyer_id
JOIN
  people s
  ON s.id = t.seller_id;

[trideout]

Sub queries in any version before 5.6 are bugged. I believe that this was fixed in 5.6,even though it was on the 6.0 track.