Geek Answers Handbook

MySQL adding a connection slows down the entire query

SELECT journey.code, journey.departure, journey.end, group_concat(pattern_road.latitude)
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
LEFT JOIN pattern_road ON pattern_road.section = pattern.section
WHERE journey_day.day = 5 AND TIME(NOW()) BETWEEN journey.departure AND journey.end
GROUP BY journey.code

The above query takes about 100 ms to complete. I am happy with this, but now I need to add another connection, when I try to do this, the request slows down to about 2 seconds.

This is a new slow request with an additional connection:

SELECT journey.code, journey.departure, journey.end, group_concat(pattern_road.latitude) AS road, group_concat(link.stop) AS stop
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
LEFT JOIN pattern_road ON pattern_road.section = pattern.section
INNER JOIN link ON link.section = pattern.section
WHERE journey_day.day = 5 AND TIME(NOW()) BETWEEN journey.departure AND journey.end
GROUP BY journey.code

Notes:

The additional connection also works in the same column as the previous connection (pattern_road), I can only think that this should be the cause of the problem. For example, if I replace the pattern_road connection with the link connection, the request will return to 100 ms, I just cannot use both connections and start it with a frequency of 100 ms.

Database Schema / Indexes in SQL Fiddle

Any ideas why this is happening? Thanks in advance.

0
2

?

SELECT  SQL_NO_CACHE journey.code, journey.departure, journey.end,
  (select group_concat(pattern_road.latitude) from  pattern_road where pattern_road.section = pattern.section) AS road,
  (select group_concat(link.stop) from link where link.section = pattern.section) AS stop
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
WHERE journey_day.day = 5 AND TIME(NOW()) BETWEEN journey.departure AND journey.end
GROUP BY journey.code
0

, . *.

pattern(code, section) pattern(section, code) ( , , ).

*

0

More articles:


All Articles