Indexes have nothing to do with Primarykeys or unique fields.
Indexes are used to organize data in a table and to find the requested row in
the shortest possible time.
There are two ways a Sql-Server table is organized
a) Heap
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.
<--- This is the current state of your products table and is obviously the worst case szenario for finding things fast.
Imagine a library where all books are not sorted in the shelves from A to Z instead put in the shelves in no particular order
b)Indexed
The data rows are stored in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. The pages in each level of the index, including the data pages in the leaf level, are linked in a doubly-linked list.
When you install NopCommerce it is impossible to install an index, since the Products don't come with the installation
and are being inserted after that.
An Index once built should be rebuilt when new Rows are added so the system can finde things faster.
For more info on that
see here