I have this query that orders prices based on the customer's NO being a possible pricegroup too (this is nicely cached as an ID in the pricegroups table too tho).
SELECT p.`variant_id`, p.`unit_price`, p.`unit_list_price`, v.`discount_perc` FROM `prices` p LEFT JOIN `variants` v ON v.`id` = p.`variant_id` WHERE ( p.`pricegroup_id` = (SELECT `id` FROM `pricegroups` WHERE `code` = ? LIMIT 1) OR p.`pricegroup_id` = ? OR p.`pricegroup_id` = (SELECT `id` FROM `pricegroups` WHERE `code` = '' LIMIT 1) ) AND p.`currency_id` = ? ORDER BY p.`variant_id`, (p.`pricegroup_id` = (SELECT `id` FROM `pricegroups` WHERE `code` = ? LIMIT 1)) DESC, (p.`pricegroup_id` = ?) DESC
The parameters are:
customerNo customerPriceGroupID customerCurrencyID customerNo customerPriceGroupID
The values of 1 and 4 and 2 and 5 are identical but used to sort in a certain way.
I tried multiple INDEX variations on the
currency_id, also implicitly making the the INDEX BTREE be DESC for
The logic behind this query is that the sorting ensures that the first
variant_id found is the correct price for this customer (the CURSOR loop holds a buffer on it to check if the variant is already seen before and only keeps the first one).
On my iPad simulator this query takes about 180-200ms (~30k rows), meaning that on an older iPad (3 and 4) this will takes seconds. I'm really at a bind here because this is really the last thing that still shows up as a slow-flag in my own code in all the tweaking that I've done through the Time Profiler in the Xcode Instruments.
currencies table has an INDEX on
id and the
pricegroups table has an INDEX on
id and separate one on