Forum PHP.pl

Czy ktoś może mi powiedzieć, jakie są zasady tworzenia przez MySQL tabel
tymczasowych przy wykonywaniu zapytań SELECT?

Spotykam się niejednokrotnie, że analiza zapytania (EXPLAIN SELECT ...)
ujawnia w kolumnie EXTRA wartość "USING TEMPORARY". Podejrzewam, że ma to
negatywny wpływ na wydajność.

Zapytanie mam takie:

[SQL] pobierz, plaintext 
EXPLAIN SELECT
Oddzialy.NazwaOddzialu,
SUM(IF(DATE_FORMAT(Transakcje.DataWpisania,'%Y-%m-%d') = CURRENT_DATE,1,0))
AS IloscTransakcjiWpisanych
FROM Transakcje, Uzytkownicy, Oddzialy
WHERE Transakcje.KtoWpisal = Uzytkownicy.IDUzyt AND Oddzialy.IDOddzialu = Uzytkownicy.IDOddzialu
GROUP BY Oddzialy.IDOddzialu
[SQL] pobierz, plaintext 

a więc zupełnie proste... Ten IF jest nie do uniknięcia - zapytanie ma
zwracać sumę i ilość transakcji różnych typów, w powyższym przykładzie
pozostawiłem jedynie jedną kolumnę. Tak czy owak nawet powyższe zapytanie
generuje "USING TEMPORARY" dla tabeli Transakcje. Indeksy są na wszystkich
złączeniach, a mimo to MySQL nie korzysta z indeksu "KtoWpisal". Dlaczego?

MySQL 4.0.18

Pozdrawiam,
Krzysiek

Przy grupowaniu - zawsze wykorzystywane jest temporary, jednak zazwyzaj nie odbija się to w zbyt drastyczny sposób na wydajności.

Natomiast co do index - jeśli możesz - udostępnij strukturę interesującego nas fragmentu bazy z przykładowymi danymi (najlepiej podając plik do pobania, a nie wklejając ją tu)

Oczywiście, ale niestety nie dysponuję serwerem dostępnym dla juzerów spoza firmy. Strukturę bazy w zakresie, który może być istotny, wklejam więc poniżej:

[SQL] pobierz, plaintext 
CREATE TABLE `Transakcje` (
  `IDTransakcji` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Status` enum('N','E','B','Z','O','G','U','R') NOT NULL DEFAULT 'N',
  `Rodzaj` enum('S','KH','KBH','PH','KK') NOT NULL DEFAULT 'S',
  `Kwota` double(10,2) NOT NULL DEFAULT '0.00',
  `Waluta` enum('PLN','EUR','CHF') NOT NULL DEFAULT 'PLN',
  `KtoWpisal` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `DataWpisania` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (`IDTransakcji`),
  KEY `IDTransakcji` (`IDTransakcji`),
  KEY `KtoWpisal` (`KtoWpisal`),
  KEY `Status` (`Status`)
) TYPE=MyISAM;
 
INSERT INTO `Transakcje` VALUES (1511,'E','S',2153.44,'PLN',1, '2004-10-06 
16:25:16');
 
CREATE TABLE `Uzytkownicy` (
  `IDUzyt` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ImieINazwisko` varchar(60) NOT NULL DEFAULT '',
  `IDOddzialu` int(11) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (`IDUzyt`),
  KEY `IDOddzialu` (`IDOddzialu`)
) TYPE=MyISAM;
 
INSERT INTO `` VALUES (1, 'Janko Walski', 20);
 
CREATE TABLE `Oddzialy` (
  `IDOddzialu` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Nip` varchar(13) NOT NULL DEFAULT '',
  `Nazwa` varchar(60) NOT NULL DEFAULT '',
  PRIMARY KEY  (`IDOddzialu`),
  KEY `IDOddzialu` (`IDOddzialu`),
  KEY `Nip` (`Nip`)
) TYPE=MyISAM;
 
INSERT INTO `Oddzialy` VALUES (1, '123-456-78-90, 'Oddział Pierwszy z Brzegu');
[SQL] pobierz, plaintext 

Będę wdzięczny za pomoc w rozwiązaniu problemu, "pałuję" się już z nim dłuższy czas i nie mam pomysłu co dalej.

Dziękuję i pozdrawiam,
Krzysiek

Moja propozycja

[SQL] pobierz, plaintext 
SELECT Oddzialy.Nazwa, SUM(
IF (
DATE_FORMAT( Transakcje.DataWpisania, '%Y-%m-%d' ) = CURRENT_DATE, 1, 0 )
) AS ilosc
FROM Oddzialy LEFT JOIN Uzytkownicy ON ( Oddzialy.IDOddzialu = Uzytkownicy.IDOddzialu )
LEFT JOIN Transakcje ON ( Uzytkownicy.IDUzyt = Transakcje.KtoWpisal )
GROUP BY Oddzialy.IDOddzialu
[SQL] pobierz, plaintext 

Zastosowałem się do Twojej propozycji. Ciekawe...

Oczywiście kolejność skanowania tabel jest inna. Teraz pierwsza przeszukiwana jest tabela Oddzialy. Ale bez użycia indeksu IDOddzialu - dlaczego?

Tego indeksu nie ma nawet w kolumnie POSSIBLE_KEYS...

Liczba przeskanowanych rekordów jest dokładnie ta sama co poprzednio. Wygląda na to, że specjalnego przyrostu wydajności nie osiągnąłem. Ale czy ktoś potrafi wytłumaczyć, dlaczego pierwsza ze skanowanych tabel (niezależnie na którą wypadnie) zawsze przeszukiwana jest bez wykorzystania indeksu?

Pozdrawiam,
Krzysiek

1. nie ma sensu tworzyć dodatkowego indeksu IDOddzialu skoro kolumna ta jest kluczem głownym (primary) Kolumna taka jest domyślnie indeksowana.

2. Nie wiem dlaczego nie wymienia żadnego indesku w possible_keys ale wydaje mi się, że najprostrzą i najprawdziwszą odpowiedzią jest taka, że nie potrzebuje żadnego indexu.
Bo sam powiedz - do trzego miałby go używać, jeśli po tej pierwszej tabeli nie ma nawet sortowania?

3. Liczba przeskanowanych rekordów.
Ta mogłaby się zmienić dopiero wtedy, gdybyśmy ograniczyli ilość rekodów dołączanych. Można by to zrobić przy pomocy np. dodania warunku związanego z datą (znajdującego sie teraz w select) do JOIN transakcje i być moze zmodyfikowanie wykorzystywanego przez date indexu,
Pisałeś jednak, że z pewnych względów nie możesz modyfikować tej struktury, więc nie tworzyłem takiego zapytania.