Mysql Order By Calculated Value - User Defined Weightings of Categories

Question

Hi All,

I have a small problem with some late additions to a project, mysql and PHP.

We have a table in mysql with 200,000 rows, each with an assigned category. Originally the select statement would just pull out the required items based on a where clause and order by an indexed id. Very quick.

However we had some users request the ability to give weightings to each category (ie. financial = 60%, credit = 25%, insurance = 15%) and enable a tickbox to degrade results with time, meaning that the select statement must now calculate a value (category * weighting) and if the tick box is enabled (category * weighting * 1/days old) for every result returned by the where clause. It then has to order by this calculated value. As you can imagine this is slow (~10 seconds) for each query.

Now I'm not sure what the best way to proceed here is as I'm not sure how to have a table holding these calculated values for indexing (because the user can select any variation).

Also issues arise if you try and simplify the sort (order by weighting, date) as this is different obviously to (order by dated weighting) as 80% * cat * 1/8days = 0.1 and 20% * cat * 1/1day = 0.2 which is higher but would not appear higher in the first sort.

I have full access to the database and code. Does anyone have any advice on how to acheive this sort of practicality without wiping out performance?

Cheers All

Answer 1

From your description it seems that the ordering criteria can be different for each row on each query. Your only option may be to throw more hardware at the database, or use a different database.

EDIT: If the dataset is not updated frequently you may be able to identify commonly used parameter sets and precompute indexes.