Autor Zpráva
nethor
Profil
#1 · Zasláno: 11. 12. 2019, 17:49:01
Odpovědět Citovat
Zdravím,
mám 2 tabulky : 

CREATE TABLE `prodeje` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Cislo` text COLLATE utf8_czech_ci,
  `DatumPodani` datetime DEFAULT NULL,
  `DatumZplatneni` datetime DEFAULT NULL,
  `Listina` text COLLATE utf8_czech_ci,
  `TypNemovitosti` varchar(3) COLLATE utf8_czech_ci DEFAULT NULL,
  `Nemovitost` text COLLATE utf8_czech_ci,
  `Katastr` text COLLATE utf8_czech_ci,
  `KatastrC` int(6) DEFAULT NULL,
  `IdRizeni` bigint(11) DEFAULT NULL,
  `IdListina` bigint(11) DEFAULT NULL,
  `IdNemovitost` bigint(11) DEFAULT NULL,
  `IdPozemek` int(11) DEFAULT NULL,
  `Step` int(11) DEFAULT NULL,
  `BlackList` tinyint(1) DEFAULT NULL,
  `LV` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `TypNemovitosti` (`TypNemovitosti`),
  KEY `Cislo` (`Cislo`(16)),
  KEY `IdRizeni` (`IdRizeni`),
  KEY `KatastrC` (`KatastrC`),
  KEY `DatumZplatneni` (`DatumZplatneni`),
  KEY `IdPozemek` (`IdPozemek`),
  KEY `IdNemovitost` (`IdNemovitost`),
  KEY `Step` (`Step`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


CREATE TABLE `prodeje_pozemky` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `IdCenovyUdaj` int(11) DEFAULT NULL,
  `Cislo` text COLLATE utf8_czech_ci,
  `KatastrKod` int(6) NOT NULL,
  `RuianDate` date NOT NULL,
  `Vymera` int(11) DEFAULT NULL,
  `Druh` int(4) DEFAULT NULL,
  `ZpusobyVyuziti` int(4) DEFAULT NULL,
  `Point` tinytext COLLATE utf8_czech_ci,
  PRIMARY KEY (`Id`),
  KEY `IdCenovyUdaj` (`IdCenovyUdaj`),
  KEY `Druh` (`Druh`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

Když položím dotaz např.: 

SELECT
IdRizeni, Listina, `prodeje`.Cislo, DatumZplatneni , Nemovitost, KatastrC, Druh, sum(prodeje_pozemky.Vymera) as VymeraSum
FROM `prodeje` LEFT JOIN `prodeje_pozemky`
on `prodeje`.Id = `prodeje_pozemky`.IdCenovyUdaj
Where `Step` = 300
&& `TypNemovitosti` = 'PAR'
&& `KatastrC` in (608505,610003,610089,610186,610208,610283,610313,610330,610372,610470,610542,610585,610704,610771,610844,610887,610950,611026,611115,611263,611379,611484,611565,611646,611701,611743,611778,611905,612006,612014,612065,612111,612146,612171,612227,612243,612286,612405,612499,612553,633895,654132,655856,658201,712680,751910,775550,795674)
&& `Druh` in (2,3,4,5,6,7,8,10,11,13,14)
group by IdRizeni Having VymeraSum >= 1000 && VymeraSum <= 10000
order by DatumZplatneni desc Limit 100
offset 200

Trvá odpověď cca 6 s., když znovu položím stejný dotaz, trvá odpověď řádově milisekundy.
Explain: 
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    prodeje    range    PRIMARY,TypNemovitosti,KatastrC,Step    KatastrC    5    NULL    118079    Using index condition; Using where; Using temporary; Using filesort
1    SIMPLE    prodeje_pozemky    ref    IdCenovyUdaj,Druh    IdCenovyUdaj    5    d121115_nemo.prodeje.Id    1    Using where

Jde Dotaz nějak upravit/přeformulovat, aby byla rychlejší už 1. odpověď?
Kajman
Profil
#2 · Zasláno: 11. 12. 2019, 21:18:24 · Upravil/a: Kajman o 2 minuty později
Odpovědět Citovat
Možná by trochu pomohl index nad více sloupci - těžko odhadnout optimální, můžete zkusit např. (`TypNemovitosti`, `Step`, `KatastrC`) plus (`IdCenovyUdaj`,`Druh`,`Vymera`).

Join bych asi nedělal vnější (tedy smazat left) a podmínku na druh bych přidal přímo do spojení.

On ten dotaz je ale stejně divný, pokud si zapnete only full group by, tak to asi zhavaruje. Možná by bylo rychlejší udělat součet jen na dotazu z tabulky prodeje_pozemky, tam to omezit přes having (i když tam budou i jiné step a typnemovitosti) a teprve výsledek připojit k hlavní tabulce a dále to podle ní filtrovat a řadit.

A někdy je nejlevnější zrychlit výkon cpu a ssd. Protože v dotaze se nevyhnete sčítání hodnot z nalezených řádků.

Edit: a pro testování, aby to bylo vždy pomalé, můžete zakázat v dotaze použití cache 
SELECT SQL_NO_CACHE IdRizeni,...
nethor
Profil
#3 · Zasláno: 12. 12. 2019, 11:07:44 · Upravil/a: nethor o 26 minut později
Odpovědět Citovat
Kajman:
Velice děkuji za SQL_NO_CACHE, to mi hodně usnadnilo práci.

Dotaz jsem zkoušel v různých variantách, asi nejrychleji funguje v této podobě: 
SELECT SQL_NO_CACHE
IdRizeni, Listina, `prodeje`.Cislo, DatumZplatneni , Nemovitost, KatastrC, Druh, sum(prodeje_pozemky.Vymera) as VymeraSum
FROM `prodeje` JOIN `prodeje_pozemky`
on 
`prodeje`.Id = `prodeje_pozemky`.IdCenovyUdaj 
&& `Druh` in (2,3,4,5,6,7,8,10,11,13,14)
&& `KatastrC` in (608505,610003,610089,610186,610208,610283,610313,610330,610372,610470,610542,610585,610704,610771,610844,610887,610950,611026,611115,611263,611379,611484,611565,611646,611701,611743,611778,611905,612006,612014,612065,612111,612146,612171,612227,612243,612286,612405,612499,612553,633895,654132,655856,658201,712680,751910,775550,795674)
&& `TypNemovitosti` = 'PAR'
&& `Step` = 300
Group by IdRizeni, Listina, `prodeje`.Cislo, DatumZplatneni , Nemovitost, KatastrC, Druh
Having 
VymeraSum >= 1000 && VymeraSum <= 10000
Order by DatumZplatneni desc 
Limit 10
Offset 200

Tím se zrychlil cca o 20%.

Indexy nad více sloupci možná pomohly, ale pokud mohu posoudit, spíš nepatrně.

Dotaz patrně nejvic brzdí podmínka `KatastrC` in (608505,610003,....)
Když uberu čísla katastrů, podstatně se zrychlí. Zkoušel jsem ji rozepsat do KatastrC = 608505 && KatastrC = 610003 ... , ale to nepomohlo.
Nenapadá mě, jak ho přeformulovat jinak, snad poddotaz? Jde to?

Zapnutí SET sql_mode = 'ONLY_FULL_GROUP_BY';
Mě dovedlo k úpravě group by, ale nemohu se dostat přes Non-grouping field 'VymeraSum' is used in HAVING clause / Can't group on 'VymeraSum'
jedině přesunutím výpočtu do Having, to ale hledání nezrychlí, pouze se připravím o výsledek VymeraSum ve výstupu.
Kajman
Profil
#4 · Zasláno: 12. 12. 2019, 12:01:19
Odpovědět Citovat
Do joinu bych přidal jen podmínku druh, ostatní bych nechal ve where.

Jak vypadá explain s novými indexy?

V having použít sum() - v select části může zůstat také, přesunutím by se čas neměl změnit, pokud tam nemá mysql botu. Nebo to celé obalit.

V tabulce prodeje_pozemky je číslo v KatastrKod stejné, jako KatastrC v tabulce prodeje?
nethor
Profil
#5 · Zasláno: 13. 12. 2019, 11:53:51
Odpovědět Citovat
Explain: 
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    prodeje    range    PRIMARY,TypNemovitosti,KatastrC,Step,TypNemovitosti_Step_KatastrC    TypNemovitosti_Step_KatastrC    22    NULL    60684    Using index condition; Using temporary; Using filesort
1    SIMPLE    prodeje_pozemky    ref    IdCenovyUdaj,Druh,IdCenovyUdaj_Druh_Vymera    IdCenovyUdaj_Druh_Vymera    5    d121115_nemo.prodeje.Id    1    Using where; Using index

Ano, KatastrKod a KatastrC jsou stejná čísla.

Dotaz jsem ještě přepsal: 
SET sql_mode = 'ONLY_FULL_GROUP_BY';

SELECT SQL_NO_CACHE
IdRizeni
FROM `prodeje` JOIN `prodeje_pozemky`
on 
`prodeje`.Id = `prodeje_pozemky`.IdCenovyUdaj 
&& `Druh` in (2,3,4,5,6,7,8,10,11,13,14)

Where 
`KatastrC` in (608505,610003,610089,610186,610208,610283,610313,610330,610372,610470,610542,610585,610704,610771,610844,610887,610950,611026,611115,611263,611379,611484,611565,611646,611701,611743,611778,611905,612006,612014,612065,612111,612146,612171,612227,612243,612286,612405,612499,612553,633895,654132,655856,658201,712680,751910,775550,795674)
&& `TypNemovitosti` = 'PAR'
&& `Step` = 300

Group by 
IdRizeni
Having 
sum(prodeje_pozemky.Vymera) >= 1000 && sum(prodeje_pozemky.Vymera) <= 10000
Order by DatumZplatneni desc 
Limit 100
Offset 200

V selectu byla většina sloupců zbytečně (asi pozůstatek vývoje)
čas klesnul cca na polovinu, to je dobré :).

Maně mě napadá že na to jdu možná špatně. Uvedeným dotazem získám pouze IdRizeni
a teprve dalším dotazem SELECT * FROM `prodeje`IdRizeni in (...) získám kompletní data o prodeji.

Asi by bylo šikovnější získat kompletní data jedním dotazem, ale ten nevím, jak napsat, kvůli omezení součtu výměr . ... jako poddotaz? A nevím, jestli by se tím něco ušetřilo.
Časová prodleva: 5 let

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm:

0