Dlugi czas wykonywania zapytania na rozbudowanej bazie, Co myślicie o takim rozwiazaniu? Opcje

[madar]

Witam,
Opiszę problem:

tabela:
produkt
produkt2sklep (8mln)
sklep (40tys) (posiada pole id_sieci)
siec (200)

Gdy chcę pobrać produkty należące do danej sieci, robie (w uproszczeniu): product JOIN produkt2sklep JOIN sklep JOIN siec
(poniewaz sklep jest przypisany do sieci, a produkt do sklepu poprzez tabele produkt2sklep).

Poniewaz w ostatnim czasie bardzo rozrosła sie tabela produkt2sklep czas zapytan wzrósł do kilku sekund, a to stanowczo za duzo. Dodam, że używam indexów tam, gdzie potrzeba.

ROZWIAZANIE?
Czy dodac tabele product2siec, ktora bylaby uzywana tylko do tego zapytania? Chyba najwiekszym minusem tego rozwiazania jest utrata integralności, bo wtedy produkt jest przypisany do sieci poprzez bezposrednie polaczenie nową tabela, oraz przez stare polaczenie poprzez tabele sklep. Plusem natomiast jest to, ze biorac pod uwage ilosci wierszy w tabeli siec i produkt2sklep rozwiazanie to (wg mnie) znacznie skrociloby czas wykonywania zapytania.
Dla dbania o integralnosc pomyslalem, ze mozna codziennie uruchamiac skrypt, ktory bedzie aktualizowal tabele produkt2siec, zeby zawarte w niej dane byly zawsze zgodne z reszta bazy.

Co o tym myslicie? Może macie inne pomysły na rozwiazanie tego problemu?

Ten post edytował madar 15.07.2011, 11:27:54

[wookieb]

1) struktura tabel
2) zapytanie
3) moc indeksów
Tego nam brakuje do powiedzenia czegokolwiek więcej.

Powód edycji: [wookieb]:

[nospor]

4) EXPLAIN (chyba że pod 3 to miałeś na myśli (IMG:style_emoticons/default/smile.gif) )

ps: produkt może należeć do kilku sklepów?

[madar]

Tak, produkt jest przypisany do wielu sklepów, a kazdy sklep jest przypisany do jednej sieci. poniewaz produkt jets przypsiany do wielu sklepow, to tabela produkt2sklep rozrosla sie do takiej wielkosci.

W każdym razie obrazowo tabele wygladaja tak:

PRODUKT
id_produktu
...

SKLEP
id_sklepu
nazwa
id_sieci
...

SIEC
id_sieci
...

PRODUKT2SKLEP
id_produktu
id_sklepu


Tabela, ktora chce wprowadzic to:

PRODUKT2SIEC
id_produktu
id_sieci


ZAPYTANIE (pseudokod):
SELECT * FROM produkt
INNER JOIN produkt2sklep ON id_produktu=id_produktu
INNER JOIN sklep ON id_sklepu=id_sklepu
INNER JOIN siec on id_sieci=id_sieci
WHERE id_sieci=8

A po amianach byloby:
SELECT * FROM produkt
INNER JOIN produkt2siec ON id_sieci=id_sieci
INNER JOIN siec on id_sieci=id_sieci
WHERE id_sieci=8

Pobieram wybrane kolumny, nie wszystkie.
Indexy sa nalozone na wszytskich polach, ktore sa pobierane i uzywane w WHERE i JOIN.
Przejalem ten projekt, moce indexow sa poustawiane przez innych, musze doczytac o ich istotnosci - czy moc indexow duzo zmienia?

Ten post edytował madar 15.07.2011, 11:27:22

[sniver]

jakie to typy tabel?
masz klucze, indeksy pozakladane? Jeśli tak to może za mało komumn jest powstawianych do indeksów...
jeśli masz innodb to może "klucze obce" wykorzystaj - nie wiem czy coś tu pomogą, ale nie zaszkodzą...

[nospor]

indeksy pozakladane?

Nie nie zakładał. Przecież napisał tylko:

Dodam, że używam indexów tam, gdzie potrzeba.

Indexy sa nalozone na wszytskich polach, ktore sa pobierane i uzywane w WHERE i JOIN.

@madar prosiłem jeszcze o EXPLAIN z zapytania

[wookieb]

To, że założysz indeks na kazdym polu kompletnie nic nie znaczy.
1) strukture tabeli pokazuje się w ten sposób

[SQL] pobierz, plaintext 
 SHOW CREATE TABLE 'tabela';
[SQL] pobierz, plaintext 

I wklejasz nam
2) Indeksy oraz moc pokazujesz w ten sposób http://dev.mysql.com/doc/refman/5.0/en/show-index.html
3) a gdzie explain z zapytania?
4) P.s 8mln to nie jest tak dużo danych

[madar]

Ok, chcialem uniknac wnikania w faktyczna strukture bazy, ale chyba nie obejdzie sie bez tego. Ponizej potrzebne dane. Tabela "visit" to produkty, "site" to sklepy. Jesli cos bedzie niezrozumiale prosze o info

[SQL] pobierz, plaintext 
visit	CREATE TABLE `visit` (
 `id_v` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `id_p2s_v` int(10) UNSIGNED NOT NULL,
 `date_from_v` date NOT NULL,
 `time_v` time DEFAULT NULL,
 `price_v` float NOT NULL,
 `avail_v` int(11) DEFAULT NULL,
 `date_to_v` date NOT NULL,
 `promotion_id_v` int(10) UNSIGNED DEFAULT NULL,
 `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `author_id` int(11) DEFAULT '2147483646',
 `v_accepted` int(11) NOT NULL DEFAULT '3' COMMENT '1 tak ; 2 nie ; 3 nieocenione;',
 PRIMARY KEY  (`id_v`),
 UNIQUE KEY `id_product_v` (`id_p2s_v`,`date_from_v`,`date_to_v`,`promotion_id_v`),
 KEY `id_p2s_v` (`id_p2s_v`),
 KEY `price_v` (`price_v`),
 KEY `date_to_v` (`date_to_v`),
 KEY `date_from_v` (`date_from_v`),
 KEY `author_id` (`author_id`),
 KEY `v_accepted` (`v_accepted`),
 KEY `promotion_id_v` (`promotion_id_v`)
) ENGINE=MyISAM AUTO_INCREMENT=5905072 DEFAULT CHARSET=utf8
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
visit2shop	CREATE TABLE `visit2shop` (
 `v2s_id` bigint(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `v2s_shop` int(10) UNSIGNED NOT NULL,
 `v2s_visit` bigint(20) UNSIGNED NOT NULL,
 PRIMARY KEY  (`v2s_id`),
 KEY `v2s_shop` (`v2s_shop`),
 KEY `v2s_prod` (`v2s_visit`)
) ENGINE=MyISAM AUTO_INCREMENT=120537533 DEFAULT CHARSET=utf8
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
site	CREATE TABLE `site` (
 `site_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `s_name` varchar(255) NOT NULL,
 `s_address` text NOT NULL,
 `s_active` enum('0','1') NOT NULL DEFAULT '1',
 `s_charset` varchar(10) DEFAULT NULL,
 `s_key_shop` tinyint(4) NOT NULL DEFAULT '0',
 `s_open_type` tinyint(1) DEFAULT '0' COMMENT '0-dowolne, 1-curl, 2-fsock',
 `s_network` int(10) UNSIGNED DEFAULT NULL,
 `s_city` int(10) UNSIGNED DEFAULT NULL,
 `s_extId` int(10) UNSIGNED DEFAULT NULL,
 `street` varchar(64) NOT NULL,
 `zipcode` varchar(6) NOT NULL,
 `lat` varchar(12) DEFAULT NULL,
 `lng` varchar(12) DEFAULT NULL,
 `open` varchar(5) DEFAULT NULL,
 `close` varchar(5) DEFAULT NULL,
 PRIMARY KEY  (`site_id`),
 KEY `s_city` (`s_city`),
 KEY `s_network` (`s_network`)
) ENGINE=MyISAM AUTO_INCREMENT=36198 DEFAULT CHARSET=utf8
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
network	CREATE TABLE `network` (
 `network_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `n_name` varchar(255) NOT NULL,
 `n_pic` int(10) UNSIGNED DEFAULT NULL,
 `nnt_type` int(10) UNSIGNED NOT NULL DEFAULT '0',
 `is_active` tinyint(1) NOT NULL DEFAULT '1',
 `external_id` int(10) UNSIGNED DEFAULT NULL,
 `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `import` int(11) NOT NULL DEFAULT '1',
 PRIMARY KEY  (`network_id`),
 KEY `n_name` (`n_name`)
) ENGINE=InnoDB AUTO_INCREMENT=284 DEFAULT CHARSET=utf8
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
EXPLAIN SELECT id_v, id_p, name_p, specification, special, package, unit_count, unit, desc_p, price_v, n_pic, n_name, v_accepted, p2s_segment
FROM visit
INNER JOIN prod2seg ON id_p2s_v = p2s_id
INNER JOIN segment ON id_s = p2s_segment
INNER JOIN product ON id_p = p2s_product
INNER JOIN visit2shop ON visit2shop.v2s_visit = id_v
INNER JOIN site s1 ON visit2shop.v2s_shop = site_id
INNER JOIN network ON s_network = network_id
WHERE visit.author_id <>2147483646
AND date_to_v >=  '2011-07-15'
AND date_from_v <=  '2011-07-15'
AND s1.s_network =6
AND p2s_segment
IN ( 452, 463, 503, 456, 454, 455 ) 
GROUP BY id_v
ORDER BY name_p ASC 
LIMIT 0 , 1
[SQL] pobierz, plaintext 

(sorry za format tego ponizej...)

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE network const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
1 SIMPLE s1 ref PRIMARY,s_network s_network 5 const 143 Using where
1 SIMPLE visit2shop ref v2s_shop,v2s_prod v2s_shop 4 hiper_stage.s1.site_id 457
1 SIMPLE visit eq_ref PRIMARY,id_product_v,id_p2s_v,date_to_v,date_from_... PRIMARY 8 hiper_stage.visit2shop.v2s_visit 1 Using where
1 SIMPLE prod2seg eq_ref PRIMARY,unikalny,p2s_segment,p2s_product PRIMARY 4 hiper_stage.visit.id_p2s_v 1 Using where
1 SIMPLE product eq_ref PRIMARY PRIMARY 4 hiper_stage.prod2seg.p2s_product 1
1 SIMPLE segment eq_ref PRIMARY PRIMARY 4 hiper_stage.prod2seg.p2s_segment 1 Using index

zrzut explaina:
(IMG:http://www.image-share.com/upload/776/295.png)


(IMG:http://www.image-share.com/upload/777/10.png)

Widzę, że jednak opis jest zbyt szczegółowy. Tak czy inaczej zdecydowałem się na wykorzystanie dodatkwoej tabeli - wykonanie zapytania trwa teraz około 100 razy szybciej (srednio z 3 s na 0,03s), co jest juz dla mnei zadowalajacym wynikiem i póki co tak to zostawię.

Ten post edytował madar 15.07.2011, 13:11:34