Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: [MYSQL] Wyszukiwanie w bazie strasznie wolne.
Forum PHP.pl > Forum > Przedszkole
mariusz g
16.01.2008, 11:09:48
witam

serwer do testów mam w domu 1.4 Pentium, ram 384MB

Dodałem do bazy 80 000 ogłoszeń i tragedia (przy 300 ogłoszeniach śmiga aż miło)
Przy jednym użytkowniku muli a co będzie przy 1000. Nawet jak będzie serwer rakieta to i tak zamuli.
Teraz procek przy wyszukiwaniu jest na 100%

Sami zobaczcie

Wystarczy kliknąć na linka następnie szukaj. Nic z formularza nie wybierajcie bo nie wszystko jeszcze działa.

http://85.221.237.255/~Ogloszenia%20v.118/...dex.php?kolor=1
komp będzie dziś chodził do 18 00

tak wygląda zapytanie do bazy

[SQL] pobierz, plaintext 
  1. $wynik = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM $tab_dane LEFT JOIN $tab_marka ON $tab_marka.id = $tab_dane.marka 
  2.    LEFT JOIN $tab_model ON $tab_model.id = $tab_dane.model
  3.    LEFT JOIN $tab_typ ON $tab_typ.id = $tab_dane.typ
  4.    WHERE $pytanie $order LIMIT $start, $na_stronie");
[SQL] pobierz, plaintext






proszę o pomoc exclamation.gif! co robić exclamation.gif!
Cysiaczek
16.01.2008, 11:13:16
Pozakładaj indeksy na kolumny relacyjne. Mi pomoglo przy kilku joinach smile.gif

Pozdrawiam
jacus24
16.01.2008, 11:14:08
Mi dobrze działa i niczym się nie różni od innych stron z ogłoszeniami:)
mariusz g
16.01.2008, 12:29:17
każde kliknięcie na stronie to jest pytanie do bazy, klikam na szczegóły ogłoszenie to tez pytanie i przewalanie wszystkiego, w szczegółach mam poprzednie i następnie to też pytanie do bazy i przewala wszystko

może by tak przy pierwszym pytaniu wszystko wrzucić do tablice i następnie sortować itd.

Czy to dobry pomysł ?

Jak to robią profesjonaliści ?

Proszę o pomoc
Aztech
16.01.2008, 12:38:15
Jeśli pokazałbyś schemat tych tabel co używasz do joina to byłoby nam łatwiej (prawdopodobnie będzie trzeba zrobić to co powiedział ~Cysiaczek)
mariusz g
16.01.2008, 12:47:27
tak to wygląda
[SQL] pobierz, plaintext 
  1. -- 
  2. -- Struktura tabeli dla  `dane`
  3. -- 
  4.  
  5. CREATE TABLE `dane` (
  6.   `id_dane` int(11) NOT NULL AUTO_INCREMENT,
  7.   `id_uzytkownika` int(11) DEFAULT NULL,
  8.   `firma_prywatnie` char(1) DEFAULT NULL,
  9.   `data_dodania` datetime DEFAULT NULL,
  10.   `data_aktywacji` datetime DEFAULT NULL,
  11.   `status` char(1) DEFAULT NULL,
  12.   `wyroznione` year(4) DEFAULT NULL,
  13.   `fotografie` char(1) DEFAULT NULL,
  14.   `kategoria` int(11) DEFAULT NULL,
  15.   `typ_oferty` char(1) DEFAULT NULL,
  16.   `marka` smallint(11) DEFAULT NULL,
  17.   `model` varchar(5) DEFAULT NULL,
  18.   `typ` smallint(2) DEFAULT NULL,
  19.   `rocznik` smallint(11) DEFAULT NULL,
  20.   `miesiac` varchar(3) DEFAULT NULL,
  21.   `przebieg` int(11) DEFAULT NULL,
  22.   `cena` int(11) DEFAULT NULL,
  23.   `region` char(2) DEFAULT NULL,
  24.   `typ_silnika` char(10) DEFAULT NULL,
  25.   `silnik` varchar(2) DEFAULT NULL,
  26.   `pojemnosc` char(5) DEFAULT NULL,
  27.   `moc` char(5) DEFAULT NULL,
  28.   `typ_nadwozia` char(2) DEFAULT NULL,
  29.   `drzwi` char(2) DEFAULT NULL,
  30.   `kolor` varchar(20) DEFAULT NULL,
  31.   `o_1` char(1) DEFAULT NULL,
  32.   `o_2` char(1) DEFAULT NULL,
  33.   `o_3` char(1) DEFAULT NULL,
  34.   `o_4` char(1) DEFAULT NULL,
  35.   `o_5` char(1) DEFAULT NULL,
  36.   `o_6` char(1) DEFAULT NULL,
  37.   `o_7` char(1) DEFAULT NULL,
  38.   `o_8` char(1) DEFAULT NULL,
  39.   `o_9` char(1) DEFAULT NULL,
  40.   `o_10` char(1) DEFAULT NULL,
  41.   `o_11` char(1) DEFAULT NULL,
  42.   `o_12` char(1) DEFAULT NULL,
  43.   `o_13` char(1) DEFAULT NULL,
  44.   `o_14` char(1) DEFAULT NULL,
  45.   `o_15` char(1) DEFAULT NULL,
  46.   `w_1` char(1) DEFAULT NULL,
  47.   `w_2` char(1) DEFAULT NULL,
  48.   `w_3` char(1) DEFAULT NULL,
  49.   `w_4` char(1) DEFAULT NULL,
  50.   `w_5` char(1) DEFAULT NULL,
  51.   `w_6` char(1) DEFAULT NULL,
  52.   `w_7` char(1) DEFAULT NULL,
  53.   `w_8` char(1) DEFAULT NULL,
  54.   `w_9` char(1) DEFAULT NULL,
  55.   `w_10` char(1) DEFAULT NULL,
  56.   `w_11` char(1) DEFAULT NULL,
  57.   `w_12` char(1) DEFAULT NULL,
  58.   `w_13` char(1) DEFAULT NULL,
  59.   `w_14` char(1) DEFAULT NULL,
  60.   `w_15` char(1) DEFAULT NULL,
  61.   `w_16` char(1) DEFAULT NULL,
  62.   `w_17` char(1) DEFAULT NULL,
  63.   `w_18` char(1) DEFAULT NULL,
  64.   `w_19` char(1) DEFAULT NULL,
  65.   `w_20` char(1) DEFAULT NULL,
  66.   `w_21` char(1) DEFAULT NULL,
  67.   `w_22` char(1) DEFAULT NULL,
  68.   `w_23` char(1) DEFAULT NULL,
  69.   `w_24` char(1) DEFAULT NULL,
  70.   `w_25` char(1) DEFAULT NULL,
  71.   `w_26` char(1) DEFAULT NULL,
  72.   `w_27` char(1) DEFAULT NULL,
  73.   `w_28` char(1) DEFAULT NULL,
  74.   `w_29` char(1) DEFAULT NULL,
  75.   `w_30` char(1) DEFAULT NULL,
  76.   `w_31` char(1) DEFAULT NULL,
  77.   `w_32` char(1) DEFAULT NULL,
  78.   `w_33` char(1) DEFAULT NULL,
  79.   `w_34` char(1) DEFAULT NULL,
  80.   `w_35` char(1) DEFAULT NULL,
  81.   `w_36` char(1) DEFAULT NULL,
  82.   `w_37` char(1) DEFAULT NULL,
  83.   `w_38` char(1) DEFAULT NULL,
  84.   `w_39` char(1) DEFAULT NULL,
  85.   `w_40` char(1) DEFAULT NULL,
  86.   `w_41` char(1) DEFAULT NULL,
  87.   `w_42` char(1) DEFAULT NULL,
  88.   `sciezka` varchar(50) DEFAULT NULL,
  89.   `foto_a` varchar(50) DEFAULT NULL,
  90.   `foto_b` varchar(50) DEFAULT NULL,
  91.   `foto_c` varchar(50) DEFAULT NULL,
  92.   `foto_d` varchar(50) DEFAULT NULL,
  93.   `foto_e` varchar(50) DEFAULT NULL,
  94.   `foto_f` varchar(50) DEFAULT NULL,
  95.   `foto_g` varchar(50) DEFAULT NULL,
  96.   `foto_h` varchar(50) DEFAULT NULL,
  97.   `foto_i` varchar(50) DEFAULT NULL,
  98.   PRIMARY KEY  (`id_dane`)
  99. ) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=latin2 AUTO_INCREMENT=46 ;
  100.  
  101. -- --------------------------------------------------------
  102.  
  103. -- 
  104. -- Struktura tabeli dla  `osobowe_marka`
  105. -- 
  106.  
  107. CREATE TABLE `osobowe_marka` (
  108.   `id` int(11) NOT NULL AUTO_INCREMENT,
  109.   `marka` varchar(10) DEFAULT NULL,
  110.   `ile` int(11) DEFAULT NULL,
  111.   PRIMARY KEY  (`id`)
  112. ) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin2 AUTO_INCREMENT=18 ;
  113.  
  114. -- --------------------------------------------------------
  115.  
  116. -- 
  117. -- Struktura tabeli dla  `osobowe_model`
  118. -- 
  119.  
  120. CREATE TABLE `osobowe_model` (
  121.   `id` int(11) NOT NULL AUTO_INCREMENT,
  122.   `id_marka` int(11) DEFAULT NULL,
  123.   `model` varchar(10) DEFAULT NULL,
  124.   `ile` int(11) DEFAULT NULL,
  125.   PRIMARY KEY  (`id`)
  126. ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin2 AUTO_INCREMENT=13 ;
  127.  
  128. -- --------------------------------------------------------
  129.  
  130. -- 
  131. -- Struktura tabeli dla  `osobowe_typ`
  132. -- 
  133.  
  134. CREATE TABLE `osobowe_typ` (
  135.   `id` int(11) NOT NULL AUTO_INCREMENT,
  136.   `id_model` int(11) DEFAULT NULL,
  137.   `typ` varchar(10) DEFAULT NULL,
  138.   `ile` int(11) DEFAULT NULL,
  139.   PRIMARY KEY  (`id`)
  140. ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin2 AUTO_INCREMENT=5 ;
[SQL] pobierz, plaintext
Aztech
16.01.2008, 15:28:43
Strasznie masz zrobioną tą bazę:
- po pierwsze jeśli masz tabelę dane, to trzymaj w niej id modelu a nie referencję do modelu, dlatego że jeśli trzymasz tam tekst to podczas JOINa masz robione porównywanie tekstów, które jest strasznie wolne
- w tabelkach osobowe_* używasz do id typów INT, po co Ci takie duże? Jeśli nie masz tam dużo danych skorzystaj z dobordziejstwa typów TINYINT, SMALLINT, MEDIUMINT
- w tabelce dane masz np marka, która jest typu smallint(11), natomiast w tabeli osobowe_marka masz id jako int(11) => niekonsekwencja
- poznaznaczaj sobie w polach UNSIGNED (id raczej ujemne nie będzie biggrin.gif)
- poprawiłem trochę twoją bazę danych (poniżej kod) pozakładałem klucze obce, potworzyłem indeksy oraz zmieniłem typ na InnoDB
- proponowałbym Ci buforowanie odczytów, czyli wybierasz tak narapwdę tylko z bazy danych dane a następnie tylko i wyłącznie dla wyświetlanych aktualnie wierszy odczytujesz odpowiednie informacje z tabelek sobowe_*
- całej tabelki dane nie chciało mi się modyfikować

Rada:
ściągnij sobie ze strony MySQL (Administrator, Query Browser) i korzystaj z nich

Kod
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Struktura tabeli dla  `dane`
--

CREATE TABLE `dane` (
  `id_dane` int(11) NOT NULL auto_increment,
  `id_uzytkownika` int(11) NOT NULL,
  `firma_prywatnie` char(1) default NULL,
  `data_dodania` datetime default NULL,
  `data_aktywacji` datetime default NULL,
  `status` char(1) default NULL,
  `wyroznione` year(4) default NULL,
  `fotografie` char(1) default NULL,
  `kategoria` int(11) default NULL,
  `typ_oferty` char(1) default NULL,
  `id_marka` int(10) unsigned NOT NULL,
  `id_model` int(10) unsigned NOT NULL,
  `id_typ` int(10) unsigned NOT NULL,
  `rocznik` smallint(11) default NULL,
  `miesiac` varchar(3) default NULL,
  `przebieg` int(11) default NULL,
  `cena` int(11) default NULL,
  `region` char(2) default NULL,
  `typ_silnika` char(10) default NULL,
  `silnik` varchar(2) default NULL,
  `pojemnosc` char(5) default NULL,
  `moc` char(5) default NULL,
  `typ_nadwozia` char(2) default NULL,
  `drzwi` char(2) default NULL,
  `kolor` varchar(20) default NULL,
  `o_1` char(1) default NULL,
  `o_2` char(1) default NULL,
  `o_3` char(1) default NULL,
  `o_4` char(1) default NULL,
  `o_5` char(1) default NULL,
  `o_6` char(1) default NULL,
  `o_7` char(1) default NULL,
  `o_8` char(1) default NULL,
  `o_9` char(1) default NULL,
  `o_10` char(1) default NULL,
  `o_11` char(1) default NULL,
  `o_12` char(1) default NULL,
  `o_13` char(1) default NULL,
  `o_14` char(1) default NULL,
  `o_15` char(1) default NULL,
  `w_1` char(1) default NULL,
  `w_2` char(1) default NULL,
  `w_3` char(1) default NULL,
  `w_4` char(1) default NULL,
  `w_5` char(1) default NULL,
  `w_6` char(1) default NULL,
  `w_7` char(1) default NULL,
  `w_8` char(1) default NULL,
  `w_9` char(1) default NULL,
  `w_10` char(1) default NULL,
  `w_11` char(1) default NULL,
  `w_12` char(1) default NULL,
  `w_13` char(1) default NULL,
  `w_14` char(1) default NULL,
  `w_15` char(1) default NULL,
  `w_16` char(1) default NULL,
  `w_17` char(1) default NULL,
  `w_18` char(1) default NULL,
  `w_19` char(1) default NULL,
  `w_20` char(1) default NULL,
  `w_21` char(1) default NULL,
  `w_22` char(1) default NULL,
  `w_23` char(1) default NULL,
  `w_24` char(1) default NULL,
  `w_25` char(1) default NULL,
  `w_26` char(1) default NULL,
  `w_27` char(1) default NULL,
  `w_28` char(1) default NULL,
  `w_29` char(1) default NULL,
  `w_30` char(1) default NULL,
  `w_31` char(1) default NULL,
  `w_32` char(1) default NULL,
  `w_33` char(1) default NULL,
  `w_34` char(1) default NULL,
  `w_35` char(1) default NULL,
  `w_36` char(1) default NULL,
  `w_37` char(1) default NULL,
  `w_38` char(1) default NULL,
  `w_39` char(1) default NULL,
  `w_40` char(1) default NULL,
  `w_41` char(1) default NULL,
  `w_42` char(1) default NULL,
  `sciezka` varchar(50) default NULL,
  `foto_a` varchar(50) default NULL,
  `foto_b` varchar(50) default NULL,
  `foto_c` varchar(50) default NULL,
  `foto_d` varchar(50) default NULL,
  `foto_e` varchar(50) default NULL,
  `foto_f` varchar(50) default NULL,
  `foto_g` varchar(50) default NULL,
  `foto_h` varchar(50) default NULL,
  `foto_i` varchar(50) default NULL,
  PRIMARY KEY  (`id_dane`),
  KEY `FK_dane_model` (`id_model`),
  KEY `FK_dane_marka` (`id_marka`),
  KEY `FK_dane_3` USING BTREE (`id_typ`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Struktura tabeli dla  `osobowe_marka`
--

CREATE TABLE `osobowe_marka` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `marka` varchar(10) NOT NULL,
  `ile` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Struktura tabeli dla  `osobowe_model`
--

CREATE TABLE `osobowe_model` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_marka` int(10) unsigned NOT NULL,
  `model` varchar(10) NOT NULL,
  `ile` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `FK_osobowe_model_marka` (`id_marka`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Struktura tabeli dla  `osobowe_typ`
--

CREATE TABLE `osobowe_typ` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_model` int(10) unsigned NOT NULL,
  `id_marka` int(10) unsigned NOT NULL,
  `typ` varchar(10) NOT NULL,
  `ile` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `FK_osobowe_typ_model` (`id_model`),
  KEY `FK_osobowe_typ_marka` (`id_marka`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1;


--
-- Ograniczenia dla tabeli `dane`
--
ALTER TABLE `dane`
  ADD CONSTRAINT `FK_dane_typ` FOREIGN KEY (`id_typ`) REFERENCES `osobowe_typ` (`id`),
  ADD CONSTRAINT `FK_dane_marka` FOREIGN KEY (`id_marka`) REFERENCES `osobowe_marka` (`id`),
  ADD CONSTRAINT `FK_dane_model` FOREIGN KEY (`id_model`) REFERENCES `osobowe_model` (`id`);

--
-- Ograniczenia dla tabeli `osobowe_model`
--
ALTER TABLE `osobowe_model`
  ADD CONSTRAINT `FK_osobowe_model_marka` FOREIGN KEY (`id_marka`) REFERENCES `osobowe_marka` (`id`);

--
-- Ograniczenia dla tabeli `osobowe_typ`
--
ALTER TABLE `osobowe_typ`
  ADD CONSTRAINT `FK_osobowe_typ_model` FOREIGN KEY (`id_model`) REFERENCES `osobowe_model` (`id`),
  ADD CONSTRAINT `FK_osobowe_typ_marka` FOREIGN KEY (`id_marka`) REFERENCES `osobowe_marka` (`id`);
mariusz g
17.01.2008, 17:56:30
witam

ogromne dzięki za odpowiedz do tematu:

teraz baza działa o 150% szybciej ale jak wchodzę na inne serwisy w necie to nie ma porównania - u nich to po prostu rakieta


pisałeś:

- proponowałbym Ci buforowanie odczytów, czyli wybierasz tak narapwdę tylko z bazy danych dane a następnie tylko i wyłącznie dla wyświetlanych aktualnie wierszy odczytujesz odpowiednie informacje z tabelek sobowe_*

podpowiesz mi jak mam sie do tego zabrać (wszystkiego uczę sie metodą prób i błędów i wertuje neta w poszukiwaniu informacji)

to co dopisałeś do bazy to dla mnie czarna magia ale poklikam w necie i może nauczę się jak samemu to optymalizować

dzięki
Aztech
18.01.2008, 08:19:34
Polecam Ci przeczytanie artykułu Jak działa MySQL na EIOBA. Polcam Ci też ostatnie wydanie phpSolutions, znajdziesz tam artykuł MySQL - Techniki przyśpieszania zapytań. Gazetka kosztuje 30zł, ale to wydanie wyjątkowo jest warte swojej ceny.
Z ciekawych artykułów poczytaj sobie tu i tu i na koniec artykulik w wiki
SirZooro
18.01.2008, 14:28:37
Możesz też zerknąć na artykuł który opisuje jak zoptymalizować bazę danych.
mariusz g
21.01.2008, 07:24:48
mam jeszcze jedno pytanie

Jakiej szybkości mam oczekiwać ?

Aby poćwiczyć zrobiłem prostą bazę z 4 kolumnami id, marka, model, typ,cena, rok
Wprowadziłem do niej 50 000 różnych danych.

Przy pomocy Query Browser wyświetliłem całą zawartość i otrzymałem czas 1,6 s i nie wiem czy to szybko, ale chyba nie bo przecież są bazy po kilka milionów rekordów a przy takim wyniku to strasznie wolno.

Jakiej szybkości mam oczekiwać ?
Ile średnio wam zajmują zapytania z taką ilością danych
Rozumiem że to kwestia struktury bazy, czy są indexy, jakie dane, ile kolumn itd...
Po prosty nie wiem do jakieś wydajności dążyć. A przydała by się np 1 000 000 rekordów i wynik w 2s. - tabela - id, marka, model, typ,cena, rok itd z 20 pozycji.

pozdrawiam
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.