I store an object in a database described by a variety of integer attributes. The real object is a bit more complicated, but now suppose that I store cars in my database. Each car has many integer attributes to describe the car (i.e. maximum speed, wheelbase, maximum power, etc.), and they are searchable by the user. The user determines the preferred range for each of the objects, and since there are many attributes, most likely there will be no vehicle that matches all attribute ranges. Therefore, the query should return several machines sorted by best match.
Currently, I have implemented this in MySQL using the following query:
SELECT *, SQRT( POW((a < min_a)*(min_a - a) + (a > max_a)*(a - max_a), 2) +
POW((b < min_b)*(min_b - b) + (b > max_b)*(b - max_b), 2) +
... ) AS match
WHERE a < (min_a - max_allowable_deviation) AND a > (max_a + max_allowable_deviation) AND ...
ORDER BY match ASC
where a and b are attributes of the object, and min_a, max_a, min_b and max_b are user-defined values. Basically, a match is the square root of the sum of the squared differences between the desired range and the actual value of the attribute. A value of 0 means perfect match.
The table contains several million records, and the WHERE clause is introduced only to limit the number of records on which the calculation is performed. The index is placed on all requested records, and the request takes 500 ms. I would like to improve this number, and I am looking for ways to improve this query.
, , . , NoSQL - . MongoDB, ().
- , , MySQL?