[MySQL][PHP] Zliczanie wariacyjne Opcje

[d0m1n1k_]

Witam,
napisałem skrypt który ma za zadanie zliczenie ile jest przypisanych wpisów do pojazdów których nazwa docelowa lub nazwa tymczasowa jest zgodna ze wzorcem z tabeli stats_id.
Wszystko działa zgodnie z założeniami, ale niestety działa bardzo wolno.
Dla minimalizacji obciążenia serwera (poleasingowy sprzęt sprzed dekady zmonotwany na poddaszu) stosuję zapis do pliku i pętlę która uruchamiana jest raz dziennie.
Ale nie zmienia to wszystko faktu, że zapytanie - a raczej pętla - wykonuje swoją pracę przez wiele minut. Wynika to z wielkości bazy danych - sumarycznie blisko 15 milionów wpisów we wszystkich tabelach.

Poniżej skrypt o którym mowa:

[PHP] pobierz, plaintext 
@include_once("inc_db.php");
set_time_limit(0);
$query_si = mysql_query("SELECT `si_id`, `si_showid` FROM `stats_id` ORDER BY `si_id`");
while($qsi = mysql_fetch_array($query_si)){
	$ilosc = mysql_fetch_array(mysql_query("SELECT COUNT(*) AS wynik_aut FROM `vehicles` WHERE `vh_name` LIKE '%" . $qsi['si_showid'] . "%' OR `vh_temp` LIKE '%" . $qsi['si_showid'] . "%'"));
	$si_id = $qsi['si_id'];
 
	$mnozniki = mysql_fetch_array(mysql_query("SELECT (SELECT COUNT(*) FROM `stats_price` WHERE `sp_si` = '" . $si_id . "') AS cnt_price, (SELECT COUNT(*) FROM `vh_bodytype` WHERE `vb_si` = '" . $si_id . "') AS cnt_bodytp, (SELECT COUNT(*) FROM `vh_engines` WHERE ve_si = '" . $si_id . "') AS cnt_engine, COUNT(*) AS cnt_milage FROM `stats_milage` WHERE `sc_si` = '" . $si_id . "'"));
 
	$elemental = $ilosc['wynik_aut'] * ($mnozniki['cnt_price'] + $mnozniki['cnt_bodytp'] + $mnozniki['cnt_engine'] + $mnozniki['cnt_milage']);
	//echo "Wynik dla #" . $si_id . " :";
	//echo " " . $mnozniki['cnt_price'] . ",";
	//echo " " . $mnozniki['cnt_bodytp'] . ",";
	//echo " " . $mnozniki['cnt_engine'] . ",";
	//echo " " . $mnozniki['cnt_milage'] . ". ";
	//echo "Koncowo: " . $elemental . "<br />";
	$eco[] = $elemental;
 
}
 
echo array_sum($eco);
[PHP] pobierz, plaintext 

Z góry dziękuję za każdą pomoc.

[Pyton_000]

Ja się wcale nie dziwie że zamula...

Pokaż strukturę tabelek i jakies przykładowe dane bo mam wrażenie że to wszystko da się uprościć

[d0m1n1k_]

Zgodnie z wnioskiem podaję strukturę (nazwa_tabeli: kolumny) i w nawiasach przykładowe wartości:

[SQL] pobierz, plaintext 
`stats_id` : `si_id`, `si_marka`, `si_model`, `si_rocznik`, `si_showid`, `si_stat`, `si_mobile`, `si_mostat`
(1, audi, a4, 1994, 1994 Audi A4, 1, 1900_9_, 0)
 
`vehicles` : `vh_id`, `vh_vin`, `vh_name`, `vh_temp`, `vh_coun`, `vh_data`, `vh_detect`, `vh_imgcheck`
(1, SAJAAxxxxxxxxxx9, 2001 Jaguar S-Type, \'01 Jaguar S-Type 4.0L, 94, data(), 9, 1)
 
`stats_milage` : `sc_id`, `sc_si`, `sc_milage`, `sc_count`, `sc_data`
(1, 1, 212837, 7, data())
 
`stats_price` : `sp_id`, `sp_si`, `sp_price`, `sp_count`, `sp_data`
(1, 1, 8543, 7, data())
 
`vh_bodytype` : `id`, `vb_si`, `type`
(1, 1054, 1)
 
`vh_engines` : `ve_id`, `ve_si`, `ve_nazwa`, `ve_pojemnosc`, `ve_moc_km`, `ve_przyspieszenie`, `ve_vmakskm`, `ve_spal_trasa`, `ve_spalanie_avg`, `ve_spal_miasto`, `ve_zbiornik`
(1, 1077, 520i, 1991, 150, 10.000, 220, NULL, 9.10, NULL, 70)
[SQL] pobierz, plaintext 

Na marginesie, czas trwania 1 godzina 1 minuta 3 sekundy (wynik 429.853).

Ten post edytował d0m1n1k_ 20.01.2016, 14:48:17

[Pyton_000]

a nie mogłeś dać po prostu dump SQL z BD? Tak to przynajmniej można sobie zaimportować tabele...

[d0m1n1k_]

Zrzut db? Pełnej? 15mln wpisów to blisko 7.5GB plik ;-)

[CuteOne]

[SQL] pobierz, plaintext 
SELECT
  (SELECT COUNT(*) FROM b WHERE b.name LIKE '%a.id%' OR ...) AS c1,
  (SELECT COUNT(*) FROM c WHERE c.id =  a.id_2 OR ...) AS c2
  ...
FROM
  a
[SQL] pobierz, plaintext 

1. Czy masz poprawnie założone indeksy na kolumnach, po których szukasz?
2. Na jakim silniku stoją te tabele - MyISAM, InnoDB czy coś innego?
3. Na razie olej ORDER BY, i sprawdź za pomocą EXPLAIN co (poza LIKE) zamula zapytanie

[Pyton_000]

Co tam 7,5GB Ja pracuję obecnie na bazach blisko 100GB

Nie nie całą, schema i jakiegoś screena albo po 100 rekordów z każdej wymaganej.

[d0m1n1k_]

[SQL] pobierz, plaintext 
SELECT
  (SELECT COUNT(*) FROM b WHERE b.name LIKE '%a.id%' OR ...) AS c1,
  (SELECT COUNT(*) FROM c WHERE c.id =  a.id_2 OR ...) AS c2
  ...
FROM
  a
[SQL] pobierz, plaintext 

1. Czy masz poprawnie założone indeksy na kolumnach, po których szukasz?
2. Na jakim silniku stoją te tabele - MyISAM, InnoDB czy coś innego?
3. Na razie olej ORDER BY, i sprawdź za pomocą EXPLAIN co (poza LIKE) zamula zapytanie

1. Yyy... tak(?) mi się wydaje. Całość prawidłowo funkcjonuje, jedynie pętla którą tu opisujemy długo (baaardzo długo - >1h) się wykonuje.
2. MariaDB
3. Co to da? Możesz rozwinąć myśl?

Co tam 7,5GB Ja pracuję obecnie na bazach blisko 100GB

Nie nie całą, schema i jakiegoś screena albo po 100 rekordów z każdej wymaganej.

Dla mnie 7.5GB jest caałkiem pracochłonną wielkością.
100GB - chylę czoła i współczuję.
Do godzinki czasu wrzucę po 50 wpisów dla każdej z tabel.

Link do zrzutów tabelek db: mini_zrzut.rar

[Pyton_000]

Huhh

po kolei:

-

Kod

SELECT COUNT(*) AS wynik_aut FROM `vehicles` WHERE `vh_name` LIKE '%" . $qsi['si_showid'] . "%' OR `vh_temp` LIKE '%" . $qsi['si_showid'] . "%'

Do tabeli `vechicles` trzeba dodać index. Nalepiej (vh_name, vh_temp)
Zmieniłbym te Like na = czyli:

Kod

SELECT COUNT(*) AS wynik_aut FROM `vehicles` WHERE `vh_name` = '" . $qsi['si_showid'] . "' OR `vh_temp` = '" . $qsi['si_showid'] . "'

Dlaczego? Ponieważ przy LIKE w takiej formie nie będą używane indeksy nawet jeśli będą założone. A to przez % na początku.
Jeśli masz najnowszą wersję MySql to możesz założyć index FullText. Jeśli nie to zwykły indeks podwójny. (Ale Like i tak trzeba zmienić)

- Dalej załóż indeksy w tabelach vh_engines (ve_si) i vh_bodytype (vb_si)


To tak na bardzo szybko. Po południu jeszcze zerknę, ale dobrze by było te zapytania z pętli zlikwidować (a przynajmniej zredukować do 1)