Multiple query to columns indexed in MySql I am trying to make a table with server-side page, so it can also apply filters for each of the columns in my table. This is its structure:

+----------------+
|    products    |
|----------------|
| id       int   |
| title    string|
| date     date  |
| category string| 
+----------------+


so it occurred to me to create a composite index for the columns that I want to filter:

CREATE INDEX index_name 
ON products(c2,c3,c4);


The problem, as the documentation says, is that the query optimizer cannot use the index to perform searches if the columns do not form a prefix to the left of the index. then I could not make a WHERE condition to a specific column if it is not in the correct order of the index, which is a problem for me, since my query could include one or more conditions in any order, according to the user's need for example:

SELECT * FROM products WHERE title = ""
SELECT * FROM products WHERE date = ""
SELECT * FROM products WHERE category = ""
SELECT * FROM products WHERE title = "" AND date = ""
SELECT * FROM products WHERE title = "" AND date = "" AND category = ""


So an index of multiple columns is not the best option for my case, so I thought to add an individual id to each of my columns, is this a good practice or is there another way to address this problem?
This index will take care of 3 of your queries:

INDEX(title, date,  -- in either order
      category)     -- last


Then you will need two more indexes to handle the other two queries.  Assuming you use the order above, then these could be the other two.

INDEX(category)
INDEX(date)


If you have a dozen columns and users can filter on virtually any combination of them, then all you can do is guess at what is best.  Some general rules:


don't make more than a dozen indexes
use about 3 columns in each index
be sure to start some of them with the most commonly used filters


But also, ...


If the filtering is on a range (eg, price range), it should be last in the index.
Don't listen to people who talk about cardinality when arguing for a particular order of columns in a composite index.
If title is too big to be indexed, then leave it out of all indexes.  Do not use "prefix indexing" like title(20).


More discussion:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql