Dwa zapytania zabijaja mi baze

Cześć,

Bawię się trochę z danymi GTFS i mam problem z dwoma zapytaniami. Lokalnie (SQLite) działają bez problemu, schody zaczynają się dopiero gdy używam ich w skrypcie PHP z moją bazą MySQL.

1. Pobierz wszystkie przystanki powiązane z daną linią autobusową

Próbuję dwóch zapytań:

SELECT T3.stop_id, T3.stop_name 
FROM trips AS T1
JOIN
stop_times AS T2
ON T1.trip_id=T2.trip_id AND route_id =  <route_id>
JOIN stops AS T3
ON T2.stop_id=T3.stop_id
GROUP BY T3.stop_id, T3.stop_name

Oraz

SELECT stop_id, stop_name FROM stops WHERE stop_id IN (
  SELECT DISTINCT stop_id FROM stop_times WHERE trip_id IN (
    SELECT trip_id FROM trips WHERE route_id = <route_id>));

2. Do infromacji o przystanku dodaj informację o wszystkich liniach jakie z niego odjeżdzają

Tutaj akurat nie zabijam bazy danych ale dziala to bardzo powoli.

SElECT * FROM (
   SELECT stop_id, stop_name, stop_lat, stop_lon, zone_id 
   FROM stops WHERE stop_id = 97
) a INNER JOIN (
   select st.stop_id, GROUP_CONCAT( distinct rt.route_short_name) as routes
   from routes rt, trips tr, stop_times st
   where rt.route_id = tr.route_id
   and tr.trip_id = st.trip_id
   and st.stop_id = 97
) b ON a.stop_id = b.stop_id

Oraz

select stops.stop_id, 
stops.stop_name, 
stops.stop_lat, 
stops.stop_lon, 
stops.zone_id, 
GROUP_CONCAT( distinct rt.route_short_name) as routes
from routes rt, trips tr, stop_times st, stops
where rt.route_id = tr.route_id
and tr.trip_id = st.trip_id
and st.stop_id = stops.stop_id
and st.stop_id = 97
group by st.stop_id

Czy mógłby mi ktoś poradzić jak to przeskoczyć?

Dziękuję i pozdrawiam!

Może wierszy w bazie jest dużo? Masz indeksy założone na pola po których robisz selekcje / grupowanie?

podaj plan zapytania obu to coś będzie można powiedzieć

Musialem na chwile porzucić projekt, ale teraz do niego wróciłem. Użyłem jakiegoś skryptu który wgrywa dane do bazy, tworzac tabele itp. Dzieki temu zapytanie

SELECT T3.stop_id, T3.stop_name 
FROM trips AS T1
JOIN
stop_times AS T2
ON T1.trip_id=T2.trip_id AND route_id = <routeid>
JOIN stops AS T3
ON T2.stop_id=T3.stop_id
GROUP BY T3.stop_id, T3.stop_name

...zaczęło działać. Jednak czas realizacji równy 20 sekundom nie jest satysfakcjonujacy.

Podaję jak wyglądają struktury tabel używanych w tym apytaniu:

DROP TABLE IF EXISTS stop_times;
CREATE TABLE stop_times(
	trip_id INT(6),
	arrival_time TIME,
	departure_time TIME,
	stop_id INT(5),
	stop_sequence INT(3),
	INDEX(trip_id),
	INDEX(stop_id)
);
 
DROP TABLE IF EXISTS stops;
CREATE TABLE stops(
	stop_id INT(5),
	stop_code CHAR(4),
	stop_name VARCHAR(200),
	stop_lon DOUBLE,
	stop_lat DOUBLE,
	PRIMARY KEY(stop_id),
	INDEX(stop_name),
	INDEX(stop_lon),
	INDEX(stop_lat)
);
 
DROP TABLE IF EXISTS trips;
CREATE TABLE trips(
	trip_id INT(6),
	service_id INT(1),
	route_id INT(3),
	shape_id INT(5),
	block_id INT(6),
	INDEX(route_id),
	INDEX(trip_id),
	INDEX(service_id)
);

Nie rozumiem trochę tych indeksów. Tzn wspomagają one szybkość wyszukiwania danych w bazie. Czyli w tym przypadku powiienem w tabeli 'stops' ustawic indeks tez na stop_id?

Pzdr

Próbowałeś tutaj dodać aliasy ?

AND route_id = <routeid>

abrakadaber napisał(a):

podaj plan zapytania obu to coś będzie można powiedzieć

Hm.. wiecie naprawde niewiele wiem o bazach danych. Do tej pory wystarczalo mi tworzenie tabel i wrzucanie danych przy podstawowej wiedzy (klucz glowny, typy danych etc). W tym zapytaniu mam do przekopania sporą ilość danych, stąd jest pewien problem.

Moim celem jest uzyskanie listy przystanków na drodze wskazanej linii. Rozumiem że muszę uzyć do tego trzech tabel: stops, stop_times i trips. Jeśli ktoś wytłumaczylby mi jak powinna wygladać strukutra tych tabel zeby działało to optymalnie, byłbym niezmiernie wdzięczny.

primary_key

Nie wiem co to za silnik bazy ale sprobowalbym przeniesc warunek route_id do klauzuli where. Jeśli dobrze czytam to jest to kolumna z tablicy trips.

Wtedy w uproszczeniu wykonanie zapytania bedzie wygladac tak:

1. Wybranie wierszy z trips gdzie route_id = wartosc. Tu zostanie uzyty index.
2. Dolaczenie tablicy stop_times po trip_id dla kazdego wiersza z 1. Do szukania rekordow w stop_times znow powinien zadzialac index
3. Dolaczenie tablicy stops po stop_id z 2 i znow uzcie indeksu.

W tej formie co masz teraz w pierwszym kroku brane sa wszykie elemnty z tablicy trips, pozniej do kazdego nastepuje proba dolaczenia tablicy stop times przy czy dodatkowo za kazdym razem sprawdzany jest warunek route_id. Przynajmniej tak jest w wiekszosci silnikow.

To co sprawdzasz w warunkach ON powinny byc tylko wiazaniem lub zawerzeniem po kolimnach tabeli dolaczanej. Reszte sprawdzaj w were, w ten sposób odcinasz pokazna czesc danych i korzytsasz z indeksow.

Czesc,

Dzieki za odpowiedzi. Ustawilem poprawnie indeksy i zapytanie zajmuje mi teraz jakies 0.5sek :)

Pozdrawiam