Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
share|improve this question
    
Please add the table structure. –  Abdul Manaf Feb 26 '13 at 12:01
    
Does the tester index include date_sold as the first column? if not, MySQL will pull all the rows of the SALE table into a temporary table to order them. And it will repeat that for all 14933 rows of the primary query. –  Thomas Jones-Low Feb 26 '13 at 12:03
    
Thomas, I've updated my question with the description of the tester index, and yes, date_sold is the first column. –  Silfverstrom Feb 26 '13 at 12:05
    
Can you paste the output of show create table sale\G –  Abdul Manaf Feb 26 '13 at 12:10
1  
Have you tried this with an index tester (buyer_id, date_sold, seller_id)? The reason I'm asking is the subquery runs better if the join column is the first column of the index. And I don't remember (it depends upon mysql version) if the order by is smart enough to use the second column in the index. –  Thomas Jones-Low Feb 26 '13 at 12:16

2 Answers 2

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 !!!

share|improve this answer

As per @Thomas Jones-Low advice, you can try adding two indexes:

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

And please, rewrite the query so it doesn't use this MySQL-only syntax of referencing Select aliases in places that they normally couldn't be referenced:

SELECT s.buyer_id AS bid, 
      (SELECT s2.seller_id
       FROM sale AS s2
       WHERE s2.buyer_id = s.buyer_id
       ORDER BY s2.date_sold LIMIT 1)
FROM sale AS s
WHERE s.seller_id = 35514335 ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.