2
\$\begingroup\$

My application is for the use of comparing a product sold by several merchants on a daily basis. A process runs to load the current values of data for each store and enters it into a database along with any previous data. Later I plan to create graphs and such off of the history of data.

SELECT `a`.`part_number`, GROUP_CONCAT(`a`.`seller_id` ORDER BY `price` ASC), GROUP_CONCAT(`b`.`part_id` ORDER BY `price` ASC), GROUP_CONCAT(`b`.`price` ORDER BY `price` ASC)
FROM `parts`
LEFT JOIN (
           SELECT * 
           FROM (
                 SELECT * 
                 FROM `parts_data` AS `tmp_a`
                 ORDER BY `date_added` DESC
                ) AS `tmp_b`
           GROUP BY `part_id`
          ) AS `b`
ON `a`.`part_id` = `b`.`part_id`
GROUP BY `a`.`part_number`

To explain the query a little, I need to select the most recent price information for several part IDs and group them together with a selected part number for comparison. Also, upon concatenation the grouped items are sorted by price to be highest to lowest.

Each part number also can have any number of item IDs associated with it so there is no implication of how many companies are being watched.

part_number |  column1  |  column2  |  column3
++++++++++++++++++++++++++++++++++++++++++++++++
            | seller:A1 | seller:A3 | seller:A6
  153231    | id:x423   | id:x534   | id:x902
            | cost:23   | cost:34   | cost:57
++++++++++++++++++++++++++++++++++++++++++++++++
            | seller:A3 | seller:A1 | seller:A6
  345123    | id:x313   | id:x631   | id:x652
            | cost:78   | cost:86   | cost:99
++++++++++++++++++++++++++++++++++++++++++++++++
            | seller:A5 | seller:A1 |
  231756    | id:x663   | id:x291   |
            | cost:35   | cost:52   |
\$\endgroup\$
1
  • \$\begingroup\$ The above is horrid in my eyes, but achieves exactly as I need. The nested selects with ORDER and GROUP work appropriately and the query returns the grouped data in the example. Given that this query works, how can it be optimized since MySQL does not allow an ORDER BY to occur before a GROUP BY? \$\endgroup\$
    – Dan
    Commented Jan 13, 2012 at 4:50

1 Answer 1

1
\$\begingroup\$

The ORDER BY inside the inner-most sub-select is pointless - it's going to get thrown out. Also, you should never rely on MySQL's behaviour of selecting a 'random' value for non-grouped columns - always explicitly specify the transformation that needs to happen on the data. You should also review what you think your Company columns are, as I am going to pretty much guarantee that the company isn't the same for every cell in the column.


EDIT:

Actually, I was thinking your ORDER BY in the GROUP_CONCAT() was just fine - I have issues with how you're doing your inner-most SELECT... ORDER BY (that you then do a GROUP BY off of, without specifying your transforms).
Assuming that the tuple [parts_data.part_id, parts_data.date] is unique (although, that's the date of what? Insert, update, your birthday? Please name more descriptively), here's how I would have structured this (in a system without CTEs and OLAPs):

SELECT `a`.`part_number`, GROUP_CONCAT(`a`.`seller_id` ORDER BY `b`.`price` ASC), 
                          GROUP_CONCAT(`b`.`part_id` ORDER BY `b`.`price` ASC), 
                          GROUP_CONCAT(`b`.`price` ORDER BY `b`.`price` ASC)
FROM `parts` as `a`
LEFT JOIN (`parts_data` as `b`
           INNER JOIN (SELECT `part_id`, MAX(`date`) AS `date`
                       FROM `parts_data`
                       GROUP BY `part_id`) as `c`
           ON `b`.`part_id` = `c`.`part_id` 
           AND `b`.`date` = `c`.`date`)
ON `a`.`part_id` = `b`.`part_id`
GROUP BY `a`.`part_number`
ORDER BY `a`.`part_number` 

Please note that I do not in fact have a MySQL instance to test against. I also don't know if this will be faster for your database (please note we now have a self-join). However, it's much clearer what you're expecting the system to do, meaning it's easier to maintain.


EDIT:

Here's an alternate version, which may be slightly faster. I don't work with MySQL (and I have some fairly serious hardward backing me up on DB2), so I'm not up on all the ins and outs.

SELECT `a`.`part_number`, GROUP_CONCAT(`a`.`seller_id` ORDER BY `b`.`price` ASC), 
                          GROUP_CONCAT(`b`.`part_id` ORDER BY `b`.`price` ASC), 
                          GROUP_CONCAT(`b`.`price` ORDER BY `b`.`price` ASC)
FROM `parts` as `a`
LEFT JOIN (SELECT `b`.`part_id`, `b`.`price`
           FROM `parts_data` as `b`
           WHERE NOT EXISTS (SELECT '1'
                             FROM `parts_data` as `c`
                             WHERE `c`.`part_id` = `b`.`part_id`
                             AND `c`.`date` > `b`.`date`)) as `b`
ON `b`.`part_id` = `a`.`part_id`
GROUP BY `a`.`part_number`
ORDER BY `a`.`part_number`

You may want to try removing the ORDER BY a.part_number clause, and see if that helps.

\$\endgroup\$
5
  • \$\begingroup\$ I updated the example table to reflect this more appropriately, but the sellers are not defined by the columns. I admit this was confusing originally, but the change not should help. \$\endgroup\$
    – Dan
    Commented Jan 13, 2012 at 4:15
  • \$\begingroup\$ The ORDER BY is not thrown out when the query is run. Without it I receive the first records that match from that table which are the oldest. The use of the ORDER BY is to select the table sorted newest to oldest and then pass that to the next table which uses those records to group on the part id. I don't know how I could prove it to you, but that is just the reality of it. \$\endgroup\$
    – Dan
    Commented Jan 13, 2012 at 4:17
  • \$\begingroup\$ I made a modification to the query to resolve the non-grouped columns issue, but it require yet another nested select. This query works and alter/removing and of the ORDER or GROUP cause wrong results. \$\endgroup\$
    – Dan
    Commented Jan 13, 2012 at 4:51
  • \$\begingroup\$ I ran your query and on average I received 17.5000 sec as the average with a high of 37.9568. However, for my two variations, I had .0119 with the ORDER BY in the GROUP_CONCAT and .0283 when opting to do an extra SELECT instead of the ORDER BY in the GROUP_CONCAT. These result are base on two days of data amounting to just 730 records (365 unique products). Any ideas why your query would result in such a way? \$\endgroup\$
    – Dan
    Commented Jan 13, 2012 at 18:25
  • \$\begingroup\$ Indicies, or rather, a lack thereof, most probably. You're going to want one on [part_id, date] if you don't have it already. I'm assuming MySQL has some sort of ExplainPlan generator - see what that outputs. \$\endgroup\$ Commented Jan 13, 2012 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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