( SELECT NAME
WHERE ID = table1.id
) AS NAME
WHERE LOC_ID IN (%s)
AND START_DATE != END_DATE
AND START_DATE <= '2002-01-31'
AND END_DATE >= '2002-01-01';
With these indexes:
The date range you have is essentially impossible to optimize; having multiple indexes gives the Optimizer a choice; it will pick from them based on the distribution of the data. That is, changing the month in question may lead to using a different index.
The indexes starting with
LOC_ID are an optimization for (1) a single LOC being used, or (2) LOC does a better job of filtering than either date.
The subquery will fail if the relationship is 1:many; I am guessing that it is 1:1. Why have table2? Usually (not always) it is unwise to have two tables in a 1:1 relationship.
SELECT * unless you really need all the columns.
EXPLAIN SELECT ...,
SHOW CREATE TABLE, and how many rows are in the table.
What version of MySQL? (Newer versions will perform this query better.)