Ive得到了数千种产品和50个左右的用户进行身份验证表。

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.

林guesing我需要某种单独的表的，其中包含了PRODUCT_ID，USER_ID和订单列订单？

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

我如何做到这一点的最有效的在MySQL，以非常快，不能慢下来，如果我得到数以百万计的产品在数据库中。

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.

它甚至明智做到这一点在MySQL或我应该使用一些其他指数一样的Solr / Lucene的？

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"

我需要的功能的一个很好的例子是谷歌搜索，你可以订购/苏preSS的结果，如果你已经登录。

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

推荐答案

嗯，首先，如果你在一个页面上显示万种产品，并最终以百万计，这将是缓慢的。我假设你以某种方式筛选下来到每页一个理智一些。

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.

反正你加入到你的product_order表将是相当快的：它是对主键和一个常数（用户ID），无论是整数，这将是一个快速的索引查找。有几个问题，我可以看到。首先，每个用户真的要定义一个亿的产品排序？假设没有秩序=显示最后，还有一个问题：

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

在 ORDER BY 的限制之前发生。 MySQL有加盟的每次的行，然后那种巨大的加入（你好文件排序）。然后它扔掉999,990出1,000,000行。

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.

如果事实证明，每个人只销售一些产品，那么这个问题就不会发生：在where子句将MySQL的唯一连接和排序几排。如果每个人卖几百万，你很可能将不得不做一些非规范化，这样就可以执行你所有的过滤 products_order ，这也将避免大规模的数量的行。你会在需要大量的行 products_order 不过，每个（产品，用户）的组合......你看到的痛苦无论采用哪种方式，很遗憾。

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.