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