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