Skip to content
Advertisement

How to improve union query?

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

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

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

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

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

I hope that makes it clearer, thanks r

Advertisement

Answer

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).

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

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

DEMO

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement