given a product table

id   some_column
1    10
2     2
3     8

when comparing all them I get a triangular matrix that I load from a csv file

id  product_left  product_right   result
1              1              2        3
2              1              3        8  
3              2              3        2

where I want to perform a query that returns all the best n comparisons for a given product

SELECT * FROM COMPARISION WHERE ( product_left = 1 OR product_right = 1 ) ORDER BY result DESC LIMIT n

I've made an index for product_left and another for product_right column

But I'm not convinced that it will scale for tens of thousands products

ie: 20k products will produce ~200 million comparisons the load takes 15 hours in regular desktop computer and it will get slower to load with more indexes

so, which should be the best strategy to scale it?

  • partitioning
  • do a different query
  • index the result column to help order by clause
  • ...

you can find the solution here https://github.com/rubentrancoso/cosinsimilarity

  • When you load a file, do you add the data to what's been loaded in the past, and process all of it? or, do you load an process a file (or, if appropriate, a batch of files) independently of what you loaded the last time you ran the process? If the latter, you might want to try loading the data without the indexes (leave on a clustered index, if you have one), and then add the indexes after the data's been loaded. This can be faster, sometimes. – RDFozz Mar 25 '17 at 1:55
  • If you will actually be running queries specifying one and only one product ID at a time, then adding the result column to both indexes (sorted in descending order) would almost certainly improve query performance. It might help even if you're querying multiple product IDs (but not as much). – RDFozz Mar 25 '17 at 1:59
  • Also, you might try splitting your query into two (one just looking at product_left, the other at product_right) combining the top results, and seeing what that does to performance. Check query plans too. This might get MySQL to use your indexes; when it has to look at product_left and product_right for every entry anyway, it might decide a table (or clustered index) scan would be better, whereas in actuality the checks of the two indexes would turn out to be faster. (Note that, in most cases, a query optimizer does make good choices - however, there are always exceptions). – RDFozz Mar 25 '17 at 2:06

This will probably run faster:

( SELECT * FROM Comparision WHERE product_left  = 1
           ORDER BY result DESC  LIMIT n
( SELECT * FROM Comparision WHERE product_right = 1
           ORDER BY result DESC  LIMIT n

If there is no change of fetching the same row from the two SELECTs, change to UNION ALL to be a little faster.

Have these two 'composite' indexes:

INDEX(product_left,  result)
INDEX(product_right, result)

Yes, the ORDER BY and LIMIT are duplicated. If you also need OFFSET, then it gets more complex, but still possible.

Why? With the indexes I gave you, each SELECT will optimally filter (WHERE) and sort (ORDER BY), thereby touch only n rows. Then the UNION puts together the 2*n rows, re-sorts and delivers the desired n.

With just INDEX(product_left) it will gather all the 1 rows, sort them and peel off n -- slower.

Without the UNION, the query will simply scan the entire table, ignoring your indexes (or any others). (OK, there is a chance of some index being usable, but what I provided is better.) Use EXPLAIN SELECT ... to see what is going on. (Ask for help it it is not obvious.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.