I have a table that saves the needed information to send notifications to users. now the UID columns in this table is almost unique because mostly each user subscribes with one device.
but it is possible to have multiple rows with same UID so unique index is out of picture and because of almost unique nature of data i don't think usual index be useful either as it's going to take almost same time to search index as whole table.
Currently the solution that comes to my mind is saving primary code of each row of notifications table in a column in Users table in comma separated format.
Best Answer
An index doesn't need to be unique to be efficient, and in fact I've rarely found making my indexes unique made much of a difference in terms of performance, in my experience. (It does in certain cases, data size dependent, but not enough to be measurably important to me so far.) On that note, my experience is within Microsoft SQL Server, so knowing which database system, version, and implementation (when applicable) would be helpful here.
That said, in most modern Relational Database Management Systems (RDBMS), the standard index is a B-Tree index. That means your data is stored in a B-Tree logical data structure which is much more efficient for searching than the entire unindexed Table itself. It doesn't matter if your data is "almost" unique vs perfectly unique. If your data was completely redundant (every value for the column(s) you anticipate indexing are exactly the same) then indexing probably won't help you much, but in your case a regular B-Tree index sounds like it would work very good.