Prośba o pomoc przy optymalizacji zapytania Opcje

[grzemach]

Witam,
W bazie mam ok 1,3mln rekordów, jest dosyć dużo zapytań, acz nie trwają one długo bo ok 4s każde. Niestety suma sumarum, zapytania te przeciążają bazę mojego hostingodawcy - i często strona jest niedostępna. A że głównie to się dzieje przy odwiedzających botach...

tabele są dwie, ale zapytanie o które mi się rozchodzi nawiązuje tylko do jednej:

[SQL] pobierz, plaintext 
CREATE TABLE `PageRankHosts` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `link` varchar(150) character SET utf8 collate utf8_unicode_ci DEFAULT NULL,
  `pr` tinyint(4) DEFAULT '0',
  `created` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `fileWithDomains` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `statusCreated` varchar(50) character SET utf8 collate utf8_unicode_ci DEFAULT NULL,
  `statusNow` varchar(50) character SET utf8 collate utf8_unicode_ci NOT NULL DEFAULT '',
  `domainCreated` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `lastMod` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `optionInfo` varchar(55) character SET utf8 collate utf8_unicode_ci NOT NULL,
  `registerIn` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `lastUpdate` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `text` tinyint(3) UNSIGNED DEFAULT '0',
  `status` tinyint(3) UNSIGNED NOT NULL DEFAULT '3',
  `fullWhoIS` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `zwolniona` int(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `link` (`link`),
  KEY `created` (`created`),
  KEY `pr` (`pr`)
);
[SQL] pobierz, plaintext 

Przykładowe zapytanie wygląda tak:

[SQL] pobierz, plaintext 
SELECT * FROM PageRankHosts ORDER BY `created` DESC, `pr` DESC LIMIT 926100,30
[SQL] pobierz, plaintext 

Jeśli wstawię zamiast * kolumny które mnie interesują (w sumie wszystkie) - nie ma różnicy. Różnica jest gdy usunę "order by", ale przy usunięciu pr, jest to ok 1s, jednak jest to rozwiązanie nie do końca satysfakcjonujące, bo tracę całość sortowania, przenieść sortowanie do PHP? Owszem, ale wyciąganie >200MB danych z bazy do php i tam obrabianie, nie będzie szybsze. Nawet wyciągnięcie samych tych dwóch kolumn nie usprawni tego, bo później trzeba dociągnąć pozostałe dane.

Proszę o wszystkie sugestie.

Ten post edytował grzemach 10.03.2011, 23:36:03

[sn1p3r]

[...]

[SQL] pobierz, plaintext 
SELECT * FROM PageRankHosts ORDER BY `created` DESC, `pr` DESC LIMIT 926100,30
[SQL] pobierz, plaintext 

wrzuć to, co Ci podaje:

explain select * from PageRankHosts order by `created` DESC, `pr` DESC limit 926100,30

Wstępnie: baza musi zrobić full scan i później zrobić sorty - gdzie indeksy się na nic nie zdają (w tym przypadku)

[grzemach]

[SQL] pobierz, plaintext 
id 	select_type 	TABLE 	type 	possible_keys 	KEY 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	PageRankHosts 	ALL 	NULL 	NULL 	NULL 	NULL 	1245506 	USING filesort
[SQL] pobierz, plaintext 

Wstępnie: baza musi zrobić full scan i później zrobić sorty - gdzie indeksy się na nic nie zdają (w tym przypadku)

Wiem, że musi zrobić full skan (sortowanie) i to 2 razy (dwa sortowania). Ale nie mam pomysłu jak przy takiej ilości danych to można by zoptymalizować. Może przerobić bazę, albo wybierać całość danych z bazy i przerabiać w PHP (ale to wydaje się bardzo głupim i złym pomysłem, który nie wierzę że zadziała szybciej).

Może by zmienić tym bazy na innoDB z MyISAM, ale jak widziałem testy wydajności to w tak prostych zapytaniach (bez transakcji) - myisam jest szybsza.

Ten post edytował grzemach 14.03.2011, 12:36:55

[Noidea]

Po założeniu indeksu (jednego!) na dwie kolumny: created i pr oraz pobieraniu samego id EXPLAIN zaczyna wyglądać obiecująco:

[SQL] pobierz, plaintext 
EXPLAIN SELECT id FROM PageRankHosts ORDER BY created DESC, pr DESC LIMIT 926100, 30
[SQL] pobierz, plaintext 

Kod

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    PageRankHosts    index    NULL    created_pr    6    NULL    1    "Using index"

jednak sprawdzam to na pustej bazie, bo nie chce mi się generować miliona wierszy.

W każdym bądź razie załóż ten indeks i sprawdź czy takie zapytanie będzie szybsze:

[SQL] pobierz, plaintext 
SELECT prh.*
FROM PageRankHosts prh
JOIN (
    SELECT id 
    FROM PageRankHosts 
    ORDER BY created DESC, pr DESC 
    LIMIT 926100, 30
) prh2 ON ( prh.id = prh2.id )
[SQL] pobierz, plaintext 

Ten post edytował Noidea 14.03.2011, 14:10:01

[grzemach]

o założeniu indeksu na obie te kolumny nie pomyślałem.

Po jego wykonaniu, moje zapytanie nie zmieniło prędkości (4s), ale Twoje wykonuje się ok 2s, więc wynik jest zadowalający. Fakt, chciałbym aby było to <1s, ale przy tej liczbie danych, raczej mało prawdopodobne...

Zwiększyłem ilość danych pobieranych do 100, przez co będzie mniej stron i mniejsze zapotrzebowanie na CPU DB

Ten post edytował grzemach 14.03.2011, 15:35:19