I have a big query with different tables queried with joins and with WHERE CLAUSES.
Now from my understanding the best index to have is to see the WHERE CLAUSE and add it as an index
select name from Table WHERE name = 'John'
WHERE table1.field = 'x' and table2.field = 'y' etc...
SELECT ... FROM tA JOIN tB WHERE tA.x = tB.y WHERE tA.name = 'foo' AND tB.name = 'bar'
tA: INDEX(name, x) tB: INDEX(name, y)
On the other hand:
SELECT ... FROM tA JOIN tB WHERE tA.name = tB.name
INDEX(name) on both tables.
name is the
PRIMARY KEY on each table, then those indexes are redundant and should not be added.