Tarek Tarek - 4 months ago 16
MySQL Question

MySQL indexes optimisation

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'

We would have an index on the "name" field .

How would we determine the best index to have if the clause looks like this:

WHERE table1.field = 'x' and table2.field = 'y' etc...

of course the query is much more complicated than that , just want to know how to proceed and if you guys have a better idea .

    FROM tA
    JOIN tB WHERE tA.x = tB.y
    WHERE tA.name = 'foo'
      AND tB.name = 'bar'

begs for

tA: INDEX(name, x)
tB: INDEX(name, y)

On the other hand:

    FROM tA
    JOIN tB WHERE tA.name = tB.name

needs INDEX(name) on both tables.

If name is the PRIMARY KEY on each table, then those indexes are redundant and should not be added.