Optymalizacja zapytania Opcje

[kmkm2]

Witam, potrzebuję pomocy przy optymalizacjia zapytania. Zapytania co prawda działa, ale jest bardzo wolne. Proszę o wskazówki w rozwiązaniu problemu.
Struktura tabel:

[SQL] pobierz, plaintext 
-- phpMyAdmin SQL Dump
-- version 4.2.6deb1
-- <a href="http://www.phpmyadmin.net" target="_blank">http://www.phpmyadmin.net</a>
--
-- Host: localhost
-- Czas generowania: 14 Lut 2015, 15:45
-- Wersja serwera: 5.5.37-0ubuntu0.13.10.1
-- Wersja PHP: 5.5.3-1ubuntu2.6
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Baza danych: `electro1_serwis`
--
 
DELIMITER $$
--
-- Funkcje
--
CREATE DEFINER=`electro1`@`localhost` FUNCTION `fn_GetYearAlbumRank`(parmAlbumID INT, parmYear INT) RETURNS int(11)
BEGIN
	DECLARE retval INT;
	SELECT 5 INTO retval;
	RETURN retval;
END$$
 
CREATE DEFINER=`electro1`@`localhost` FUNCTION `get_status`(`id_zlecenia` INT) RETURNS int(11)
    NO SQL
BEGIN
DECLARE tmp int;
SELECT  id_stan_zlecenia INTO tmp FROM `stan_zlecenia` WHERE stan_zlecenia.id_zlecenia=id_zlecenia ORDER BY id_stan_zlecenia DESC LIMIT 1;
RETURN tmp;
END$$
 
DELIMITER ;
 
-- --------------------------------------------------------
 
--
-- Struktura tabeli dla tabeli `klienci`
--
 
CREATE TABLE IF NOT EXISTS `klienci` (
`id_klienci` int(11) NOT NULL,
  `imienazwisko` varchar(200) COLLATE utf8_polish_ci NOT NULL,
  `nazwisko` varchar(200) COLLATE utf8_polish_ci NOT NULL,
  `adres` varchar(100) COLLATE utf8_polish_ci NOT NULL,
  `miasto` varchar(50) COLLATE utf8_polish_ci NOT NULL,
  `telefon` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `nip` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `mail` varchar(200) COLLATE utf8_polish_ci NOT NULL,
  `firma` varchar(200) COLLATE utf8_polish_ci NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1375 ;
 
-- --------------------------------------------------------
 
--
-- Struktura tabeli dla tabeli `stan_zlecenia`
--
 
CREATE TABLE IF NOT EXISTS `stan_zlecenia` (
`id_stan_zlecenia` int(11) NOT NULL,
  `id_zlecenia` int(11) NOT NULL,
  `uwagi` text COLLATE utf8_polish_ci NOT NULL,
  `id_statusy` int(11) NOT NULL,
  `data_status` int(11) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3144 ;
 
-- --------------------------------------------------------
 
--
-- Struktura tabeli dla tabeli `statusy`
--
 
CREATE TABLE IF NOT EXISTS `statusy` (
`id_statusy` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8_polish_ci NOT NULL,
  `kolor` varchar(7) COLLATE utf8_polish_ci NOT NULL,
  `waga` int(11) NOT NULL,
  `termin` tinyint(4) NOT NULL,
  `widoczny` tinyint(4) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=14 ;
 
-- --------------------------------------------------------
 
--
-- Struktura tabeli dla tabeli `zlecenia`
--
 
CREATE TABLE IF NOT EXISTS `zlecenia` (
`id_zlecenia` int(11) NOT NULL,
  `id_klienci` int(11) NOT NULL,
  `id_logowanie` int(11) NOT NULL,
  `rodzaj_sprzetu` varchar(50) COLLATE utf8_polish_ci NOT NULL,
  `model` varchar(200) COLLATE utf8_polish_ci NOT NULL,
  `numer_seryjny` varchar(200) COLLATE utf8_polish_ci NOT NULL,
  `producent` varchar(200) COLLATE utf8_polish_ci NOT NULL,
  `wyposazenie_dodatkowe` varchar(200) COLLATE utf8_polish_ci NOT NULL,
  `opis_usterki` text COLLATE utf8_polish_ci NOT NULL,
  `przyczyna_usterki` varchar(50) COLLATE utf8_polish_ci NOT NULL,
  `proba_naprawy` tinyint(1) NOT NULL,
  `uwagi` varchar(200) COLLATE utf8_polish_ci NOT NULL,
  `max_koszt_naprawy` int(11) NOT NULL,
  `data` int(11) NOT NULL,
  `kto` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `ladowarka` tinyint(1) NOT NULL,
  `torba` tinyint(1) NOT NULL,
  `dysk` tinyint(1) NOT NULL,
  `bateria` double NOT NULL,
  `przyczyna` enum('samoistna','upadek','zalanie','inna') COLLATE utf8_polish_ci NOT NULL,
  `dostarczenie` enum('osobiscie','kurier','samwysylka') COLLATE utf8_polish_ci NOT NULL,
  `priorytet` enum('bezplatna','platna','express','') COLLATE utf8_polish_ci NOT NULL,
  `archiwum` tinyint(1) NOT NULL,
  `login_allegro` varchar(50) COLLATE utf8_polish_ci NOT NULL,
  `nr_aukcji_allegro` varchar(20) COLLATE utf8_polish_ci NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=2010 ;
 
--
-- Indeksy dla zrzutów tabel
--
 
--
-- Indexes for table `klienci`
--
ALTER TABLE `klienci`
 ADD PRIMARY KEY (`id_klienci`);
 
--
-- Indexes for table `stan_zlecenia`
--
ALTER TABLE `stan_zlecenia`
 ADD PRIMARY KEY (`id_stan_zlecenia`);
 
--
-- Indexes for table `statusy`
--
ALTER TABLE `statusy`
 ADD PRIMARY KEY (`id_statusy`);
 
--
-- Indexes for table `zlecenia`
--
ALTER TABLE `zlecenia`
 ADD PRIMARY KEY (`id_zlecenia`);
 
--
-- AUTO_INCREMENT for dumped tables
--
 
--
-- AUTO_INCREMENT dla tabeli `klienci`
--
ALTER TABLE `klienci`
MODIFY `id_klienci` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1375;
--
-- AUTO_INCREMENT dla tabeli `stan_zlecenia`
--
ALTER TABLE `stan_zlecenia`
MODIFY `id_stan_zlecenia` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3144;
--
-- AUTO_INCREMENT dla tabeli `statusy`
--
ALTER TABLE `statusy`
MODIFY `id_statusy` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14;
--
-- AUTO_INCREMENT dla tabeli `zlecenia`
--
ALTER TABLE `zlecenia`
MODIFY `id_zlecenia` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2010;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 
[SQL] pobierz, plaintext 

Zapytanie:

[SQL] pobierz, plaintext 
 $sql="SELECT * 
FROM klienci
INNER JOIN zlecenia
USING (  `id_klienci` ) 
LEFT OUTER JOIN (
SELECT statusy.status,statusy.termin, stan_zlecenia.id_statusy, stan_zlecenia.id_zlecenia, stan_zlecenia.data_status, stan_zlecenia.uwagi, statusy.waga
FROM  `stan_zlecenia` ,  `statusy` 
WHERE stan_zlecenia.id_statusy = statusy.id_statusy
) AS sz
 
USING (  `id_zlecenia` ) 
 
INNER JOIN logowanie
USING( `id_logowanie` )
 
WHERE 1 $sql_wyszukiwanie $sql_logowanie
AND ( 
 
sz.data_status IS NULL 
OR 
sz.data_status = ( SELECT MAX( data_status ) AS max_data FROM stan_zlecenia WHERE stan_zlecenia.id_zlecenia = zlecenia.id_zlecenia )  
 
)
 
ORDER BY  waga,data_status DESC  $limit";
[SQL] pobierz, plaintext 

Ten post edytował kmkm2 14.02.2015, 16:46:30

[Pyton_000]

jest wolne bo w tabelach nie masz pozakładanych indeksów.

Pozakładaj indeksy na kolumny którymi łączysz tabele. Do tego na kolumny: id_klienci, id_stan_zlecenia, id_statusy, id_zlecenia załóż PrimaryKey

[kmkm2]

Indexy, są pozakładane. Zmodyfikowałem pierwszy, dołączyłem pełną definicje tabel.

[sazian]

EXPLAIN sprzed selektem i pokaż wynik

[kmkm2]

Pomogła zmiana podzapytania z

[SQL] pobierz, plaintext 
SELECT MAX( data_status ) AS max_data FROM stan_zlecenia WHERE stan_zlecenia.id_zlecenia = zlecenia.id_zlecenia ) 
[SQL] pobierz, plaintext 

na

[SQL] pobierz, plaintext 
 SELECT data_status FROM stan_zlecenia WHERE stan_zlecenia.id_zlecenia = zlecenia.id_zlecenia ORDER BY `id_stan_zlecenia` DESC LIMIT 1
[SQL] pobierz, plaintext 

.

Wynik explain:




id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2812 Using where; Using temporary; Using filesort
1 PRIMARY logowanie ALL PRIMARY NULL NULL NULL 2 Using join buffer
1 PRIMARY zlecenia eq_ref PRIMARY PRIMARY 4 sz.id_zlecenia 1 Using where
1 PRIMARY klienci eq_ref PRIMARY PRIMARY 4 electro1_serwis.zlecenia.id_klienci 1
3 DEPENDENT SUBQUERY stan_zlecenia ref id_zlecenia id_zlecenia 4 electro1_serwis.zlecenia.id_zlecenia 1 Using where; Using filesort
2 DERIVED statusy ALL PRIMARY NULL NULL NULL 12
2 DERIVED stan_zlecenia ref id_statusy id_statusy 4 electro1_serwis.statusy.id_statusy 75

[sazian]

ile rekordów jest wynikiem tego zapytania ?
jak bardzo jest wolne ?
tego OR'a z linij 20 pewnie nie możesz się pozbyć ? bo najprawdopodobniej to on wszytko psuje