How to improve union query?

Question

I have a query which is 'working' but slow and feels very poor and I don't want to have to update each time we add/remove a supplier!

Basically I have a table of 'prices' and for each suppler I need to get their price for the code based on their longest code match (this is working), I repeat this for each supplier_id and then union them together finally joining the supplier names to the results

item code ABC123456

select t1.*, s.name from
(
(Select * from prices where ABC123456 like CONCAT(code_prefix , '%') AND supplier_id = 1
ORDER BY LENGTH(code_prefix) Desc Limit 1)

UNION

(Select * from prices where ABC123456 like CONCAT(code_prefix , '%') AND supplier_id = 2
ORDER BY LENGTH(code_prefix) Desc Limit 1)

UNION

(Select * from prices where ABC123456 like CONCAT(code_prefix , '%') AND supplier_id = 3
ORDER BY LENGTH(code_prefix) Desc Limit 1)

... for each supplier

) t1

join supplier s on t1.supplier_id = s.id
order by buy_price asc

1) How can I run this automatically for any number of suppliers (ie run for each supplier in supplier table and then join the best result from each) - I cannot get my head round it

2) Performance is not great, 300 ms for each query and I have 400000 codes (in codes table) too run through. Should I be doing this as a StoredProc? would that make a big difference although it should only be run when we get a pricing update 1 or 2 times a month per supplier!

3) Is it possible to the populate a new table price_order (code, sequence) where code is from above and then sequence is the supplier_id's in price order (low to high), I can do this is an app, but is there a smarter way to do in DB for better performance?

Currently running 10.0.27-MariaDB but can possibly change if really needed!

Thanks in advance

Update requirements

prices table (other columns exist too!)

+---------------+--------------+-----------+
| code_prefix   | suppler_id   | price.    |
+---------------+--------------+-----------+
| ABC123        | 1            | 100       |
| ABC1          | 1            | 123       |
| ABC177        | 1            | 723       |
| ABC12         | 2            | 111       |
| ABC           | 2            | 222       |
| ABC111        | 3            | 001       |
| AB            | 3            | 234       |
| A             | 4            | 010       |
| B             | 4            | 710       |
+---------------+--------------+-----------+

We have another table of codes which we need to lookup against the prefix in the prices table

+---------------+
| code          |
+---------------+
| ABC123456     |
| ABC155555     |
| ABC12         |
| ABC7777777    |
+---------------+

So for each row in the code table I need the best/longest match for each supplier

so code ABC123456 will return
+---------------+--------------+-----------+
| code_prefix   | suppler_id   | price     |
+---------------+--------------+-----------+
| ABC123        | 1            | 100       |
| ABC12         | 2            | 111       |
| AB            | 3            | 234       |
| A             | 4            | 010       |
+---------------+--------------+-----------+


code ABC155555 will return
+---------------+--------------+-----------+
| code_prefix   | suppler_id   | price     |
+---------------+--------------+-----------+
| ABC1          | 1            | 123       |
| ABC           | 2            | 222       |
| AB            | 3            | 234       |
| A             | 4            | 010       |
+---------------+--------------+-----------+

we then need to sort the result by price in ascending order and concat the supplier ID's to give a supplier order

+------------+----------------+
| code       | suppler_order  |
+------------+----------------+
| ABC123456  | 4,1,2,3        |
| ABC155555  | 4,1,2,3        |
| ...        | ...            |
+------------+----------------+

I hope that makes it clearer, thanks r

You want to get the row with the longest `destination_prefix` for each `supplier_id`? — Barmar, Jun 03 '20 at 22:15
Hi Barmar, that code is working but yes, for each supplier I need to look through the prices table to get the longest code match, repeat this for each supplier, so if 10 suppliers I will end up with 10 rows, 1 for each supplier all with there individual longest code matches. destination_prefix should have been code_prefix - sorry updated! — Robert B, Jun 03 '20 at 22:24
The join with supplier doesn't change. You get the row with the longest destination prefix grouped by `supplier_id` in the subquery. — Barmar, Jun 03 '20 at 22:26
how would I do a group by supplier_id with limit 1 per supplier_id ?? — Robert B, Jun 03 '20 at 22:29
Where they use `MAX(columnname)` you want to use `MAX(LENGTH(code_prefix))` — Barmar, Jun 03 '20 at 22:31
I can do group by MAX() type queries, however this is not giving the right results ``` Select id, description, carrier_id, MAX(LENGTH(code_prefix)) from rates where 'ABC49160000000' like CONCAT(code_prefix , '%') group by supplier_id ``` is not returning the right match per supplier, it is seeming random from within the where result, not the best match — Robert B, Jun 03 '20 at 23:34
`SELECT r.* from rates AS r join (select supplier_id, MAX(LENGTH(code_prefix)) AS maxlength from rates group by supplier_id) AS r1 ON r.supplier_id = r1.supplier_id AND LENGTH(r.code_prefix) = r2.maxlength` — Barmar, Jun 03 '20 at 23:38
Hi Barmar, I have tried your code and have the same issue that I am getting 1 result per supplier however it it not the best match it just seems to be a random result from the set? any ideas — Robert B, Jun 04 '20 at 06:39
Update your question with the full query, sample data, and the desired result. I'll reopen and then we can troubleshoot it. — Barmar, Jun 04 '20 at 15:16
I have updated the question, hopefully it is clearer now. thx — Robert B, Jun 04 '20 at 15:52
Can't the `UNION DISTINCT` be eliminating by using `supplier_id IN (1,2,3)`? — Rick James, Jun 05 '20 at 21:56
Hi Rick, would that not then prevent the LIMIT 1 from getting the best match PER supplier to just be the best match across ALL suppliers? — Robert B, Jun 08 '20 at 18:12

score 0 · Accepted Answer · answered Jun 04 '20 at 17:19

First define a view to get all the rows that match each code prefix for each supplier (if you had a newer MariaDB version you could use a CTE).

CREATE VIEW supplier_codes AS
SELECT DISTINCT c.code, p.code_prefix AS code_prefix, p.supplier_id, p.price
FROM codes AS c
JOIN prices AS p ON c.code LIKE CONCAT(p.code_prefix, '%');

You can then use a common idiom for getting the row with the max length of code prefix for each supplier ID.

SELECT t1.*
  FROM supplier_codes AS t1
  JOIN (
    SELECT supplier_id, MAX(LENGTH(code_prefix)) AS maxlength
    FROM supplier_codes
    GROUP BY supplier_id
  ) AS t2 ON t1.supplier_id = t2.supplier_id AND LENGTH(t1.code_prefix) = t2.maxlength

Then you can join that with the codes table to get the grouped results for each code.

SELECT c.code, GROUP_CONCAT(DISTINCT sc.supplier_id ORDER BY sc.price) AS supplier_order
FROM codes AS c
JOIN (
  SELECT t1.*
  FROM supplier_codes AS t1
  JOIN (
    SELECT supplier_id, MAX(LENGTH(code_prefix)) AS maxlength
    FROM supplier_codes
    GROUP BY supplier_id, code
  ) AS t2 ON t1.supplier_id = t2.supplier_id AND LENGTH(t1.code_prefix) = t2.maxlength
) AS sc ON c.code = sc.code
GROUP BY c.code;

DEMO

Thanks Barnar, so nearly their I think, the results for the second query are not quite right SELECT t1.* FROM supplier_codes AS t1 using your fiddle data ABC7777777 should match ABC supplier 2 — Robert B, Jun 05 '20 at 08:27
I was struggling to understand how it would work but have just noticed in the second query you show GROUP BY supplier_id, but final you include the GROUP BY supplier_id, code. I will give another go thx — Robert B, Jun 05 '20 at 08:37
I made 1 change - "AS t2 ON t1.supplier_id = t2.supplier_id AND LENGTH(t1.code_prefix) = t2.maxlength AND t1.code_prefix = t2.code_prefix to ensure it returned the longest match for the code, really happy thanks you so much Barmar — Robert B, Jun 05 '20 at 12:23

How to improve union query?

1 Answers1