mysql field indexing

Question

I have a table products, and I have columns like product_name, product_id AND product_code. What is the best way to index these three fields? Index them one by one, or should I create one index for the combination of the three? Keep in mind that there will be a search engine for listing my products with filters for these three fields.

Answer 1

If the combination of columns is supposed to be unique then create a unique index with all three of the columns. Otherwise index the columns that are commonly used in queries. I don't think it really matters if you make a index containing many or one columns.

Answer 2

You should always index different combinations of the columns based on queries which are affecting your table. Individual column indexes are uneffective.