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
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
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 stored procedure? 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!
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
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;