Mysql – Problem with order by in MySQL subquery

MySQL

I'm running a query that runs in about 2 seconds when the subquery does not include the order by date_sold clause. However, when I add the order by clause, the query runs forever.

I'm confused, since it seems that I'm using a index for both queries, and as I've understood, the subquery should be able to use the index for the subquery. Any ideas where I went wrong? 

EXPLAIN Select buyer_id as bid, 
              (SELECT seller_id
               from sale USE INDEX(tester)
               where buyer_id = bid
               order by date_sold LIMIT 1)
         from sale where seller_id = 35514335;
+----+--------------------+-------+-------+-----------------------------------+--------+---------+-------+-------+--------------------------+
| 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 |
enter code here

The table structure 

| id               | int(11)    | NO   | PRI | NULL    | auto_increment |
| seller_id        | bigint(20) | NO   | MUL | NULL    |                |
| buyer_id         | bigint(20) | NO   | MUL | NULL    |                |
| date_acquired    | bigint(20) | NO   |     | NULL    |                |
| date_sold        | bigint(20) | NO   | MUL | NULL    |                |
| brand            | text       | NO   | MUL | NULL    |                |
| muncipality_code | bigint(20) | NO   | MUL | NULL    |                |
| new              | int(11)    | YES  |     | NULL    |                |
| car_regnumber    | varchar(6) | YES  |     | NULL    |                |
| gender           | varchar(1) | YES  |     | NULL    |                |

Tester index

| sale  |          1 | tester              |            1 | date_sold        | A         |       14840 |     NULL | NULL   |      | BTREE      |         |
| sale  |          1 | tester              |            2 | buyer_id         | A         |    11768564 |     NULL | NULL   |      | BTREE      |         |
| sale  |          1 | tester              |            3 | seller_id        | A         |    11768564 |     NULL | NULL   |      | BTREE      |         |

SHOW CREATE TABLE SALE

| sale  | CREATE TABLE `sale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` bigint(20) NOT NULL,
  `buyer_id` bigint(20) NOT NULL,
  `date_acquired` bigint(20) NOT NULL,
  `date_sold` bigint(20) NOT NULL,
  `brand` text NOT NULL,
  `muncipality_code` bigint(20) NOT NULL,
  `new` int(11) DEFAULT NULL,
  `car_regnumber` varchar(6) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `muncipality_code` (`muncipality_code`),
  KEY `brand` (`brand`(10)),
  KEY `seller_id` (`seller_id`),
  KEY `seller_id_index_asc` (`seller_id`),
  KEY `date_sold` (`date_sold`),
  KEY `tester` (`date_sold`,`buyer_id`,`seller_id`)
) ENGINE=MyISAM AUTO_INCREMENT=66390336 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED |

Best Answer

SUGGESTION #1 : Use a Different Index

ALTER TABLE sale ADD INDEX tester2 (seller_id,buyer_id,date_sold);

I suggest this order because seller_id in the query is a constant (35514335). That would make looking up all buyer_id values.

SUGGESTION #2 : Redesign the Query

From the looks of the query, you are retrieving first date_sold for buyer_id for seller_id 35514335.

SELECT buyer_id,MIN(date_sold) date_sold
FROM sale WHERE seller_id = 35514335
GROUP BY buyer_id;

If seller_id 35514335 has buyers who purchased from other sellers as well, then do this:

SET @given_seller_id = 35514335;
SELECT DISTINCT A.buyer_id,B.seller_id FROM
(
    SELECT buyer_id,MIN(date_sold) date_sold
    FROM sale WHERE seller_id = @given_seller_id
    GROUP BY buyer_id
) A LEFT JOIN sale B USING (buyer_id);

If you do not want to include seller_id 35514335 in the output, then do this:

SET @given_seller_id = 35514335;
SELECT DISTINCT A.buyer_id,B.seller_id FROM
(
    SELECT buyer_id,MIN(date_sold) date_sold
    FROM sale WHERE seller_id = @given_seller_id
    GROUP BY buyer_id
) A LEFT JOIN sale B USING (buyer_id)
WHERE B.seller_id <> @given_seller_id;

Give it a Try !!!

Related Question