Geek asks and answers

MySQL does not use INDEX in a subquery

I have these tables and queries as defined in sqlfiddle .

At first, my problem was to group people showing that LEFT JOINed is visiting the new year’s lines. This I decided to use a subquery.

Now my problem is that this subquery does not use the INDEX defined in the table visits. This causes my query to run almost endlessly on tables with approximately 15,000 rows.

Here is the request. The goal is to list each person once with his newest (by year) entry in the visit table.

Unfortunately, on large tables, it becomes real because it does not use INDEX in the 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

- , MySQL INDEX, visits?

+5
2

:

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;

  • -, SQL (, SELECT, GROUP BY, ). () .

  • -, ( ) ORDER BY , ( ) - MySQL, . , , , MySQL X ( , , ORDER BY ).

:

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-

(id_people, year) .

. , (, 30), visits:

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 ;
+4

, , , ?

, GROUP BY. GROUP BY , MAX COUNT, .

? .

    SELECT people.id, people.name, MAX(visits.year) year
      FROM people
      JOIN visits ON people.id = visits.id_people
  GROUP BY people.id, people.name

, , ( ), .

SELECT a.id, a.name, a.year, v.note
  FROM (
         SELECT people.id, people.name, MAX(visits.year) year
          FROM people
          JOIN visits ON people.id = visits.id_people
      GROUP BY people.id, people.name
  )a
  JOIN visits v ON (a.id = v.id_people and a.year = v.year)

Go fiddle: http://www.sqlfiddle.com/#!2/d67fc/20/0

- , , JOIN LEFT JOIN.

- , ORDER BY . .

: GROUP BY - . , . , .

: , , . , , . , , , . , . . http://www.sqlfiddle.com/#!2/4f644/2/0

. : visit.id , , , . .

SELECT p.id, p.name, v.year, v.note
  FROM (
         SELECT id_people, max(id) id
          FROM visits
      GROUP BY id_people
  )m
  JOIN people p ON (p.id = m.id_people)
  JOIN visits v ON (m.id = v.id)

http://www.sqlfiddle.com/#!2/4f644/1/0 , . , . , , - id.

, visit.id, , . MAX (year)... GROUP BY (id_people), MAX (id)... GROUP BY (id_people).

  SELECT v.id_people,
         MAX(v.id) id
    FROM (
         SELECT id_people, 
                MAX(year) year
           FROM visits
          GROUP BY id_people
         )p
    JOIN visits v ON (p.id_people = v.id_people AND p.year = v.year)
   GROUP BY v.id_people

(http://www.sqlfiddle.com/#!2/c2da2/1/0) - .

SELECT p.id, p.name, v.year, v.note
  FROM (
      SELECT v.id_people,
             MAX(v.id) id
        FROM (
             SELECT id_people, 
                    MAX(year) year
               FROM visits
              GROUP BY id_people
             )p
        JOIN visits v ON (     p.id_people = v.id_people 
                           AND p.year = v.year)
       GROUP BY v.id_people
      )m
   JOIN people p ON (m.id_people = p.id)
   JOIN visits v ON (m.id = v.id)

SQL - , , , , .

+2

More articles:


All Articles