Re: Record Position in Ordered Selection
Peter Brawley Wrote:
-------------------------------------------------------
> With a big table, self-joins are slow. They don't
> scale. As suggested by another poster, it can be
> faster to create creating a temp table ...
Thanks, Peter. See my response to Rick, but if that doesn't handle it, I'm going to have to find a different approach to what has turned out to be considerably more complex than I anticipated!
Here's what I'm trying accomplish: People list things for sale. Sales rank is a function of price and time -- the more it costs and the faster it sells, the higher the rank; what I'm thinking is, when an item sells, calculate some value based on its price and time since last sale, and add this to its value in the ranking table. This information is needed in two places: when a listing is viewed, its rank is displayed, and when visitors are browsing listings, they need to be order by rank. For the first use, I'd be satisfied for now with any of these solutions, or even reading in the first 1,000 records and scanning them (I don't really care about items not in the top 1,000). But of course the scanning method wouldn't work for browsing, and I'm afraid that's also where the db server would really get clobbered with the other solutions.
I do have a housekeeper module which runs every 15 minutes and could simply calculate the ranks and store them in the listing table, but I don't know of any way to do mass updates; I don't really want to be doing 1,000 update queries every 15 minutes.
Edited 1 time(s). Last edit at 06/08/2009 06:56AM by Alan Little.