Databases
StackOverflow2013
Postsdbo
POWhich DB to choose for finding best matching records?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhich DB to choose for finding best matching records?
    primarykey
    data
    text
    <p>I'm storing an object in a database described by a lot of integer attributes. The real object is a little bit more complex, but for now let's assume that I'm storing cars in my database. Each car has a lot of integer attributes to describe the car (ie. maximum speed, wheelbase, maximum power etc.) and these are searchable by the user. The user defines a preferred range for each of the objects and since there are a lot of attributes there most likely won't be any car matching all the attribute ranges. Therefore the query has to return a number of cars sorted by the best match.</p> <p>At the moment I implemented this in MySQL using the following query:</p> <pre><code>SELECT *, SQRT( POW((a &lt; min_a)*(min_a - a) + (a &gt; max_a)*(a - max_a), 2) + POW((b &lt; min_b)*(min_b - b) + (b &gt; max_b)*(b - max_b), 2) + ... ) AS match WHERE a &lt; (min_a - max_allowable_deviation) AND a &gt; (max_a + max_allowable_deviation) AND ... ORDER BY match ASC </code></pre> <p>where a and b are attributes of the object and min_a, max_a, min_b and max_b are user defined values. Basically the match is the square root of the sum of the squared differences between the desired range and the real value of the attribute. A value of 0 meaning a perfect match.</p> <p>The table contains a couple of million records and the WHERE clausule is only introduced to limit the number of records the calculation is performed on. An index is placed on all of the queryable records and the query takes like 500ms. I'd like to improve this number and I'm looking into ways to improve this query.</p> <p>Furthermore I am wondering whether there would be a different database better suited to perform this job. Moreover I'd very much like to change to a NoSQL database, because of its more flexible data scheme options. I've been looking into MongoDB, but couldn't find a way to solve this problem efficiently (fast). </p> <p><strong>Is there any database better suited for this job than MySQL?</strong></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USEwout Kleinsmann
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhich DB to choose for finding best matching records?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhich DB to choose for finding best matching records?
      1. USjeffsaracco
      1. VTFavorite
    3. VO
      singulars
      1. POWhich DB to choose for finding best matching records?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI'm missing where you actually have a problem - this sounds like premature optimization...
      singulars
      1. POWhich DB to choose for finding best matching records?
      1. USOMG Ponies
    2. COYou may want to review SQL Server or Oracle's ability to index views. Create a view that describes rows and their matches and index them.
      singulars
      1. POWhich DB to choose for finding best matching records?
      1. USta.speot.is
    3. CO@OMG: I think he means to want searches of the type: `SELECT macthCalculation FROM t WHERE (a BETWEEN amin and amax) AND (b BETWEEN bmin and max) ...` which with a few million records and searching over 2 or more attributes may be slow with BTREE indexes.
      singulars
      1. POWhich DB to choose for finding best matching records?
      1. USypercubeᵀᴹ
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload