Witam.

Od jakiegoś czasu mam problem z przeciążeniem serwera, spowodowanym nieoptymalnym połączeniem z bazą (tak twierdzi hostingodawca).
Struktura tabeli z największą ilością danych (200tys rekordów) to:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `pliki` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nazwa` text NOT NULL,
  `www` int(2) NOT NULL,
  `identyfikator` int(11) NOT NULL,
  `data_dodania` datetime NOT NULL,
  `wyswietlen` int(11) NOT NULL DEFAULT '0',
  `pobran` int(11) NOT NULL DEFAULT '0',
  `ocena` int(11) NOT NULL DEFAULT '0',
  `polecane` tinyint(1) NOT NULL DEFAULT '0',
  `blokada` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `www` (`www`,`identyfikator`),
  FULLTEXT KEY `nazwa` (`nazwa`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2 AUTO_INCREMENT=211448 ;
[SQL] pobierz, plaintext 

Przykładowe zapytanie do bazy, które powoduje obciążenie to:

[PHP] pobierz, plaintext 
$zap = 'SELECT www,identyfikator FROM pliki WHERE www="'.$www.'" and identyfikator="'.$identyfikator.'"';
		$query = mysql_query($zap) or die('Blad zapytania');
[PHP] pobierz, plaintext 

Czy ktoś mógłby mnie naprowadzić jak można to zoptymalizować?
Proszę o pomoc.

Jak duży ruch masz na tej tabeli? IMHO wystarczy ją dać na InnoDb - z tym że z fulltexta musiałbyś zrezygnować...

Niby niewielki ten ruch, bo 40-50tys odsłon dziennie to nie dużo, a jak widać nieoptymalny system stworzyłem.

@Sephirus: W tym konkretnym przypadku MyISAM może być nawet szybszy od InnoDB przy odczycie danych. Dodatkowo, jak sam zauważyłeś InnoDB w wersji MySQL 5.5 nie obsługuje indeksu pełnotekstowego (dopiero wersja 5.6 to wprowadza).

@djgarsi:
1. Jesteś pewien, że to ta tabela i to zapytanie jest wąskim gardłem? W tym przypadku mamy do czynienia z "idealną sytuacją", bo na dobrą sprawę wszystkie dane mogą być odczytane bezpośrednio z indeksu, z pominięciem tabeli samej w sobie.
2. Pamiętaj, że ilość rekordów wcale nie ma aż tak dużego znaczenia w tabeli. Najlepiej jakbyś pokazał nam slow query loga.

MyISAM jest kiepski jak jest dużo odczytów/selectów bo blokuje całą tabelę - nie wiem ale podejrzewam że to może być to akurat.

EDIT: @Crozin - musisz mi dać więcej info czemu w tym przypadku MyISAM miałby być szybszy?

Ogólnie jak w MyISAMIE na autoincremencie są dziury i jest dużo odczytów to MyISAM nie wyrabia, z tego co sam praktykuje to raczej nigdy nie używam go dla tabel, które muszą mieć szybki odczyt wiec stąd nie kumam do końca :|

@Crozin - niestety nie mam dostępu (chyba) do pliku z logami. To serwer współdzielony.

hmm bez tego będzie ciężko, musiałbyś obserwować procesy w MySQL (o ile też masz do tego dostęp - SHOW PROCESSLIST) i jak masz fart to zobaczysz jakieś długo wykonujące się zapytanie lub dużo zapytań naraz.

Może masz niewydajną aplikacje - z dużą liczbą zapytań. Z czego korzystasz do MySQL? mysql_*** czy PDO? Może udałoby Ci się policzyć ile na żądanie masz zapytań itp.

Bo tak jak zauważył Crozin to wcale nie musi być to konkretne zapytanie.

Obecnie uzywam mysql_***, jednak po tej akcji na bank przepiszę serwis na PDO.

No pasowałoby się przenieść na PDO Aczkolwiek to problemu nie rozwiąże.

W bazie masz dostęp jedynie do swojej bazy - możesz masz też dostęp do bazy "mysql" tam masz tabelę "slow_log", która ma info o tych "slow queries".

Proponuje Ci na szybko, wziąć całą apkę, zrobić backup kodu, napisać w jakimś wszędzie dołączanym pliku funkcję:

[PHP] pobierz, plaintext 
function my_mysql_query($query) {
    $t = microtime(true);
    $result = mysql_query($query);
    error_log('tu info o zapytaniu i trwaniu: '.(microtime(true) - $t));
    return $result;
}
[PHP] pobierz, plaintext 

Albo coś w tym stylu i nadpisałbym we wszystkich skryptach mysql_query => my_mysql_query. Jak dobrze przygotujesz tą funkcję, dostaniesz info jakie zapytanie ile Ci zabiera czasu i ile jest zapytań na request.

EDIT: Przede wszystkim wrzuć to zapytanie samo do bazy i zobacz w ile się wykona (nadaj jakieś tam te pola które wrzucasz z PHP) - możesz też dodać zaraz po SELECT klauzulę SQL_NO_CACHE aby mieć pewność ,że czas jest poprawny

[SQL] pobierz, plaintext 
SELECT SQL_NO_CACHE ....
[SQL] pobierz, plaintext 

Niestety nie mam takich danych "slow_log". A jak widać przydałyby się bardzo.
No cóż, na początej przepiszę system na PDO, a później będę kombinował z tą funkcją my_mysql_query (bardzo ciekawy pomysł, prosty a zarazem trudny do wpadnięcia na niego)

Dziękuję za pomoc. Pozdrawiam.

Kombinuj Ale.. nie tylko mi się pomógł należy bądźmy uczciwi ;P

Najpierw sprawdź sobie my_mysql_query. Myślę, że przejście na PDO nic nie da.

Nie wiem dokładnie jak zachowują się indeksy w MySQLu, ale może zrób indeks w kolejności KEY `www` (`identyfikator`, `www`) jeśli poprawiłoby to selektywność wybierania rekordów.
Ile rekordów zazwykle zwraca to zapytaniu ? Po zapytaniu wydaje mi się że jeden, ale wolę się upewnić, bo może jest za duzy ruch sieciowy niepotrzebnie generowany.

z tego co słyszałem to mysqli jest szybsze od pod
a na pewno będzie łatwiej przerobić z mysql_*

po przejściu na obiektowe mysqli można bardzo łatwo wykonać test "my_mysql_query", wystarczy zrobić klasę dziedziczącą po mysqli i przeciążyć query