I have a situation where I am doing lots of complex sorting on records that already exist in a MySQL table. The table has a 'sort_order' field which means I can do a simple ORDER BY sort_order to keep it nice and quick on SELECT, it's only UPDATE which is slow.
If I change the order that I want 10000 rows to be displayed, I am currently making 10000 SQL queries such as:
UPDATE products SET sort_order=0 WHERE id='5'; UPDATE products SET sort_order=1 WHERE id='2'; UPDATE products SET sort_order=2 WHERE id='32'; and so on.
Obviously I'd love to be able to put all of this into one query.
Is there some way I can combine this with IN() ? UPDATE products set sort_order=**** WHERE id IN('5','2','32');
Many thanks in advance for any help / advice.
What is the exact operation you're doing? Is there any possibility you can do "UPDATE products SET sort_order=sort_order+1 WHERE sort_order > 30" for e.g. - as in you insert an 'item' at spot 30 and want all the rest below it to be bumped up one.
You can certainly use IN(), but there is a (quite high) limit on how long the query string can be. It's pretty ridiculously high actually, but something to consider.
Personally, I'd look at how you're doing things - see if there isn't an alternative way to do it like outlined above. I'm assuming these aren't just some precalculated ordering, they're manually ordered for some reason? If it's not 'we want to manually order them' - In a products database, I would think you could just have an "ORDER BY product_group, product_name" or some such.
Cheers,
--
- Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital.
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
