Using indexes with your queries

In this section, we will go over how to see which indexes your multiple tables query is using and how to troubleshoot them. Please refer to Chapter 6, Querying a Single Table, for an introduction to the EXPLAIN syntax you will be using here.

You will begin with a query you used earlier in the chapter and will add EXPLAIN to the query to get information about which indexes your query is using. Execute the following query to get your index explanation information:

EXPLAIN SELECT p.playerid, p.birthyear,
 a.yearid, a.teamid, a.G_defense, b.H
FROM lahmansbaseballdb.appearances AS a
INNER JOIN lahmansbaseballdb.people AS p
ON p.playerid = a.playerid
INNER JOIN lahmansbaseballdb.batting AS b
ON p.playerid = b.playerid
WHERE b.yearid = 2017
AND b.H <> 0
ORDER BY p.playerid, a.yearid, a.teamid, a.G_defense, b.H;

In the following screenshot, the EXPLAIN results...