Mysql results sorted by list which is unique for each user

Question

Ive got a table of thousands of products and 50 or so authenticated users.

These users all show the products on their own web sites and they all require the ability to have them ordered differently.

Im guesing i need some kind of seperate table for the orders which contains the product_id, user_id and order column?

How do i do this the most efficiently in mysql so as to be very fast, and not slow down if i get millions of products in the database.

Is it even wise to do it in mysql or should i be using some kind of other index like solr/lucene?

My Product table is called "products" My User table is called "users"

A good example of the functionality i need is google search where you can order/supress the results if you are logged in.

edit: the product results will be paginated and the users have the authority to edit the products, so its not just ready only

Answer 1

You should Always index the occured colums in where / order / group to enhance the performance. However in your case, it really depends on the layer structure, how are the customers accessing your database?

Directly to the MySQL database so that they have the ability to execute queries?

Or do they access through a Webservice or such?

Either way, I would adapt the data-layer so that the customer can vary on the order-by statement, to increase productivity for the customer.

Answer 2

Well, first, if you're displaying thousands and eventually millions of products on a page, that's going to be slow. I assume you are somehow filtering them down to a sane number per page.

Anyway, your join to your product_order table will be fairly fast: It is on the primary key and a constant (the user id), both integers, and it will be a quick index lookup. There are a couple of problems I can see. First, is each user really going to define an ordering of a million products? Assuming no order = display last, there is another problem:

SELECT whatever
  FROM
    products p
    LEFT JOIN products_order o ON (
      p.product_id = o.product_id
      AND 1234 = o.user_id
    )
  WHERE p.stock > 0 -- some search criteria
  ORDER BY COALESCE(o.order, 999999999) --- arbitrarily large number
  LIMIT 10

The ORDER BY happens before the LIMIT. MySQL has to join every row, and then sort that huge join (hello filesort). Then it throws away 999,990 out of 1,000,000 rows.

If it turns out that each person only sells a few products, then this issue won't happen: the where clause will have MySQL only join and sort a few rows. If each person sells millions, you're probably going to have to do some denormalizing, so that you can perform all your filtering in the products_order, which will also avoid the massive number of rows. You'll need a lot of rows in products_order though, one for each (product,user) combination… You're looking at pain either way, unfortunately.

Answer 3

Can't you use a table where you'll save each user options. Have a "index robot" system ? This ways your sql query will be much faster. And why bother returning 100 000 row if the user cares only about the first 25 he or she selected as valueable ?