Multiple query to columns indexed in MySql

Question

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?

• Akina: There is NO fields with the names c2,c3,c4 in the table structure.
• Akina: This is its structure This is pornography, not structure. Post SHOW CREATE TABLE tablename output. FULL output.
• FeRcHo: It is a reference to columns 2, 3, 4
• Akina: Does ANY fields combination in WHERE clause is possible? or 5 shown combinations only? And - specify the amount of records in the table and the amount of distinct values in each separate field (approximate).

Answer 1

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