Best Indexing for Performance - MySQL I have a question related performance DB MySQL. I have querying like this with big data inside :

SELECT 
       * 
FROM 
       tableA a 
       INNER JOIN tableB b ON a.id = b.id AND a.code = b.code
       INNER JOIN tableC c ON a.uniq = c.uniq
WHERE
       a.date = '2020-01-01'
       AND a.type = 'X'
       AND b.merk =  'New';


My question is, what is better using single index or multi-column index?

Ex :

Index in tableA (Example1) :


idx1 (merk, code, date, type)
idx2 (date)
idx3 (merk)
idx4 (code)
idx5 (type)


Index in tableA (Example2) :


idx1 (merk, code, date, type)
idx2 (merk, code)
idx3 (date, type)



What is better? Example1 or Example2? And please explain it.



Notes : maybe tableA will got varian WHERE on another query for future

Thank you
The engine will only be able to make use of one index per table so while the other indexes may help you in the future with other queries, their benefit will be limited in assisting you with this particular query.

Given what we know about your query and limited knowledge of the table and the insert pattern, I would suggest the following.

Table A - (Date, Type)
Table B - (merk, ID, Code)
Table C - (Uniq)


Of course, if you were able to limit the result set columns (getting rid of the SELECT *) then further columns in the index could be of benefit.  

These indexes above will allow the engine to seek quickly and get quickly to the rows that it wants.  

I do have one question about the "merk" on Table B.  If that is something like a status that changes then I would not index on it, leaving just an index on ID and Code.  If the value of the field changes frequently then it is usually a poor index candidate as it will cause much fragmentation.