-1

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 variant_id, pricegroup_id and currency_id, also implicitly making the the INDEX BTREE be DESC for pricegroup_id and currency_id.

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.

Finally the currencies table has an INDEX on id and the pricegroups table has an INDEX on id and separate one on code.

4
  • An index on (currency_id, pricegroup_id, variant_id) might help. – ypercubeᵀᴹ Apr 29 '16 at 15:28
  • Also reading the explanations, seems like you use only the first returned row, correct? If yes, you could add LIMIT 1 to the query. – ypercubeᵀᴹ Apr 29 '16 at 15:33
  • Sadly LIMIT 1 won't work because it's for every variant_id. The result is now 51566 (about twice the variants count), which is a waste, but I don't know how to get that lighter without suffering new slowness. No combination of indexes get's me under 200ms :( – User2910293 Apr 29 '16 at 15:49
  • Show the DB schema, example data, and the desired result. – CL. May 1 '16 at 11:06
1

I very much doubt it can do anything with an index starting with prices.pricegroup_id because the where relevant clause covers multiple distinct values. An index on currency_id is liable to be useful for filtering, currency_id, pricegroup_id might perform better because it can read that value for the other WHERE clause to filter out the rows before needing to lookup the rest of the data. currency_id, pricegroup_id, variant_id may help further if it can use that ordering to avoid a separate sorting step though I suspect that not to be the case due to the complex product_id clause which I think will cause a full sort anyway.

I assume variants.id is the primary key for that table and so indexed, if not then it probably should be the PK and if that isn't true you will want a separate index on it to help that JOIN.

Those sub-queries will be helped by an index on pricegroups.code. I don't have a lot of experience with SQLite so don't have intimate knowledge of its query optimisation powers, but if it isn't bight enough to see that (SELECTidFROMpricegroupsWHEREcode= ? LIMIT 1) only need be computed once (not one per row considered by the WHERE filters and once per row output when sorting the ordering of the output) then you would see noticeable gains from looking that up separately (though test to see if this is the case - if the query planner is bright enough then you can leave this as-is). Those sub-queries do look a bit odd to me though: you are picking one row each time (LIMIT 1) without any ordering clause - so if multiple rows match you will get an arbitrary one of them out, and if you know you will only ever get zero or one row out the the sub-query (i.e. the values in pricegroups.code are unique) you don't need to explicitly state the limit.

It would be useful to add to your question which indexing arrangements you have tried and what the resulting query plans looked like (you can see the query plan using EXPLAIN QUERY PLAN).

1
  • Thank you for your answer. As you suggested I already extracted the 2 subqueries to be selected and cached separately in code. Also it will fully exclude the statement for the WHERE and ORDER BY on the pricegroup_id if there's no pricegroup based on the code being customerNo. These are very minor gains, but still a little. Also I wish I could've do GROUP BY on variant_id after the ORDER BY (which I know won't work),but it might dramatically decrease the result-set. Finally I don't get it why I couldn't make a INDEX likepricegroup, currency_id, variant_id, pricegroup DESC, pricegroup DESC.. – User2910293 Apr 29 '16 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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