MySQL is not using INDEX in subquery

Question

I have these tables and queries as defined in sqlfiddle.

First my problem was to group people showing LEFT JOINed visits rows with the newest year. That I solved using subquery.

Now my problem is that that subquery is not using INDEX defined on visits table. That is causing my query to run nearly indefinitely on tables with approx 15000 rows each.

Here's the query. The goal is to list every person once with his newest (by year) record in visits table.

Unfortunately on large tables it gets real sloooow because it's not using INDEX in subquery.

SELECT *
FROM people
LEFT JOIN (
  SELECT *
  FROM visits
  ORDER BY visits.year DESC
) AS visits
ON people.id = visits.id_people
GROUP BY people.id

Does anyone know how to force MySQL to use INDEX already defined on visits table?

Answer 1

Your query:

SELECT *
FROM people
LEFT JOIN (
  SELECT *
  FROM visits
  ORDER BY visits.year DESC
) AS visits
ON people.id = visits.id_people
GROUP BY people.id;

• First, is using non-standard SQL syntax (items appear in the SELECT list that are not part of the GROUP BY clause, are not aggregate functions and do not sepend on the grouping items). This can give indeterminate (semi-random) results.

• Second, ( to avoid the indeterminate results) you have added an ORDER BY inside a subquery which (non-standard or not) is not documented anywhere in MySQL documentation that it should work as expected. So, it may be working now but it may not work in the not so distant future, when you upgrade to MySQL version X (where the optimizer will be clever enough to understand that ORDER BY inside a derived table is redundant and can be eliminated).

Try using this query:

SELECT 
    p.*, v.*
FROM 
    people AS p
  LEFT JOIN 
        ( SELECT 
              id_people
            , MAX(year) AS year
          FROM
              visits
          GROUP BY
              id_people
         ) AS vm
      JOIN
          visits AS v
        ON  v.id_people = vm.id_people
        AND v.year = vm.year 
    ON  v.id_people = p.id;

The: SQL-fiddle

A compound index on (id_people, year) would help efficiency.

---

A different approach. It works fine if you limit the persons to a sensible limit (say 30) first and then join to the visits table:

SELECT 
    p.*, v.*
FROM 
    ( SELECT *
      FROM people
      ORDER BY name
        LIMIT 30
    ) AS p
  LEFT JOIN 
    visits AS v
      ON  v.id_people = p.id
      AND v.year =
    ( SELECT 
          year
      FROM
          visits
      WHERE
          id_people = p.id
      ORDER BY
          year DESC
        LIMIT 1
     )  
ORDER BY name ;