PHP.EE FOORUM   
Nimi:   Pass:   Mäleta mind! 
   Teemad | php.ee esilehele | registreeri | Märgi kõik teemad loetuks | #php.ee Skype vestlus | RSS
UUS TEEMA  OTSI  Lehekülgi: 1 2 
MySQL päringute optimeerimine
Postitaja: aksel 2006-03-09 15:09:58
Hei!

Tegelen ühe parajalt suure asjaga, kus on päris keerulisi päringuid ja andmeid tuleb ka järjest juurde.

Probleem, mille otsa lendasin, on selline, et üks päring võtab aega 10 ms (millisekundit), kui ma EI kasuta ORDER BY klauslit ja umbes 10 sekundit, kui ma kasutan ORDER BY klauslit.

Selgituseks - ORDER BY võib olla suvaline väli resultsetist.

Kas on mingeid häid soovitusi, mida silmas pidada, et ka koos ORDER BY-ga päringut kiiremaks saada? EXPLAIN SELECT ütleb "extra" väljal:
Using temporary; Using filesort;

Kas mingit mysql tuuninguvalikud võiks siin aidata?

Tänud ette!
RE: MySQL päringute optimeerimine
Postitaja: monte_ 2006-03-09 15:19:35
küll manualist aga siiski

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization

vähemalt peaksid pärast selle lugemist nägema kuskohast see overhead tuleb. päringut sa ju meiega jagada ei raatsinud :D
RE: MySQL päringute optimeerimine
Postitaja: monte_ 2006-03-09 15:21:59
ehk siis lühidalt kokku võttes - indekseeri kõik need väljad ära mille järgi sa tellid ORDER BY klauslis ....
RE: MySQL päringute optimeerimine
Postitaja: aksel 2006-03-09 15:31:17
TSITEERITUD:
ehk siis lühidalt kokku võttes - indekseeri kõik need väljad ära mille järgi sa tellid ORDER BY klauslis ....


Tänan vihje eest - ei aidanud seekord:) Mul on tavaliselt väljad küllaltki hästi indekseeritud, aga see selleks.

Tuli välja hoopis, et üks GROUP BY võib kurja juur olla. Raatsin ikka päringut ka siia pista, kui abi peaks olema:
SELECT `message`.`VID` AS message_VID ,
`message`.`ShortCodeVID` AS message_ShortCodeVID ,
`message`.`KeywordVID` AS message_KeywordVID ,
`message`.`DestinationAddress` AS message_DestinationAddress ,
`message`.`OriginatorAddress` AS message_OriginatorAddress ,
`message`.`Message` AS message_Message ,
`message`.`MessageId` AS message_MessageId ,
DATE_FORMAT(`message`.`Timestamp`, '%Y%m%d%H%i%s') AS message_Timestamp,
`message`.`Operator` AS message_Operator ,
`message`.`MessageStatusVID` AS message_MessageStatusVID ,
DATE_FORMAT(`message`.`Received`, '%Y%m%d%H%i%s') AS message_Received,
`message`.`Deleted` AS message_Deleted ,
COUNT(`sentmessage`.`VID`) AS MessageCount ,
`sentmessage`.`VID` AS sentmessage_VID ,
`sentmessage`.`MessageVID` AS sentmessage_MessageVID ,
`sentmessage`.`SenderVID` AS sentmessage_SenderVID ,
`sentmessage`.`UID` AS sentmessage_UID ,
`sentmessage`.`DestinationAddress` AS sentmessage_DestinationAddress ,
`sentmessage`.`OriginatorAddress` AS sentmessage_OriginatorAddress ,
`sentmessage`.`Message` AS sentmessage_Message ,
`sentmessage`.`Operator` AS sentmessage_Operator ,
DATE_FORMAT(`sentmessage`.`DateSent`, '%Y%m%d%H%i%s') AS sentmessage_DateSent,
`sentmessage`.`Deleted` AS sentmessage_Deleted ,
DATE_FORMAT(`sentmessage`.`Modified`, '%Y%m%d%H%i%s') AS sentmessage_Modified
FROM `message`
LEFT JOIN `sentmessage`
ON `sentmessage`.`messageVID`=`message`.`VID`
WHERE 1=1
GROUP BY `message`.`VID`
ORDER BY message_Timestamp
DESC LIMIT 0, 100
RE: MySQL päringute optimeerimine
Postitaja: Keegi_Teine 2006-03-09 15:34:27
assamalla

ehk siis ilus päring :P

Viimati muudetud: 09-03-2006 15:34:39
Muutja: Keegi_Teine
Põhjus:

RE: MySQL päringute optimeerimine
Postitaja: monte_ 2006-03-09 15:39:00
see miks ta sul pange paneb tuleb sellest et ta teeb andmete sorteerimiseks ajutise tabeli ja siis kukub seal neid närima ...

GROUP BY `message`.`VID`
ORDER BY message_Timestamp

group by optimeerimisest ja tööpõhimõtetetest siis samast

http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization
RE: MySQL päringute optimeerimine
Postitaja: aksel 2006-03-09 15:57:26
TSITEERITUD:
assamalla

ehk siis ilus päring :P


Kommenteeri palun täpsemalt, mis tal viga on. Siis teab ehk parandada;)

Ma ise väidan, et see on väga ilus ja absoluutselt funktsionaalne, st et seal pole üleliigseid asju ja ta teeb seda, mida tegema peab.
Selliseid päringuid koostab mu frameworgi QueryBuilder osakond, seal on lihtne üht või teist asja muuta ja kõik päringud tulevad edaspidi ilusamad.
RE: MySQL päringute optimeerimine
Postitaja: ninnunännu 2006-03-09 16:12:00
TSITEERITUD:
Ma ise väidan, et see on väga ilus ja absoluutselt funktsionaalne, st et seal pole üleliigseid asju ja ta teeb seda, mida tegema peab.
Jajaa, ilgelt ilus päring. Mõnus vaadata kohe. Saada või missikonkursile.
Veel üks "kvaliteetsüsteemide" looja või mis? Et kui ma väidan: mina teen ilusaid ja kvaliteetseid asju, siis nii ka on?

Kiidukukk
RE: MySQL päringute optimeerimine
Postitaja: aksel 2006-03-09 16:13:15
TSITEERITUD:
Jajaa, ilgelt ilus päring. Mõnus vaadata kohe. Saada või missikonkursile.
Veel üks "kvaliteetsüsteemide" looja või mis? Et kui ma väidan: mina teen ilusaid ja kvaliteetseid asju, siis nii ka on?

Kiidukukk


Ära lahmi, räägi asjast. Räägi konkreetsetest vigadest. Kuidas sina päringuid teed? Minul on selle päringuga nii, et mina pean seda ainult erandkorras lugema debugimiseks nagu praegu. Muidu koostab selle masin ja masin seda ka loeb jne. Masinal puudub arusaam päringute-esteetikast.
RE: MySQL päringute optimeerimine
Postitaja: ninnunännu 2006-03-09 16:21:03
TSITEERITUD:


Ära lahmi, räägi asjast. Räägi konkreetsetest vigadest. Kuidas sina päringuid teed? Minul on selle päringuga nii, et mina pean seda ainult erandkorras lugema debugimiseks nagu praegu. Muidu koostab selle masin ja masin seda ka loeb jne. Masinal puudub arusaam päringute-esteetikast.
Iga asja kohta öeldakse: ära lahmi. Niikui kriitilisem oled, kohe lahmid, jah?
Masin masinaks, eetika eetikaks. Sul on probleem selle päringuga, olgem ausad, ükski INIMENE ei viitsi ometi hakata tutkima sinu MASINALE tehtud päringut. Lase siis masinal seda analüüsida, äkki annab tema sulle vastuse.
RE: MySQL päringute optimeerimine
Postitaja: Keegi_Teine 2006-03-09 16:28:04
TSITEERITUD:


Kommenteeri palun täpsemalt, mis tal viga on. Siis teab ehk parandada;)


midagi...
mida ütlesin, seda mõtlesin

pole pikka aega nii pikka päringut lihtsalt näninud :)
RE: MySQL päringute optimeerimine
Postitaja: scorps 2006-03-09 16:32:12
TSITEERITUD:
Iga asja kohta öeldakse: ära lahmi. Niikui kriitilisem oled, kohe lahmid, jah?

sinu kriitikal puuduvad igasugused mõistlikud argumendid, seega sa enamasti lahmid jah.

P.S querybuilder on väga mõistlik meetod päringute loomiseks ja enamasti selliste vigade puhul peab progeja neid uurima.
RE: MySQL päringute optimeerimine
Postitaja: ninnnännu 2006-03-09 16:38:32
TSITEERITUD:

sinu kriitikal puuduvad igasugused mõistlikud argumendid, seega sa enamasti lahmid jah.

P.S querybuilder on väga mõistlik meetod päringute loomiseks ja enamasti selliste vigade puhul peab progeja neid uurima.


QueryBuilder ei tähenda, et kohe peab miski rõve päringujurakas tulema ... Loomulikult on QueryBuilder hää asi ja see ei ole vaidluse objekt.

Ahjaa, scorps, mis lahmimisse puutub. Sa pole siiani näidanud ühtegi suuremat vidinat, mis pilte DB-s hoiaks ja samal aja kiire oleks ... et siis mina lahmin ... :p
RE: MySQL päringute optimeerimine
Postitaja: aksel 2006-03-09 16:40:43
TSITEERITUD:

pole pikka aega nii pikka päringut lihtsalt näninud :)


See on üks lühemaid mul:) Noh, kas just päris nii, aga kui päringule väljade lisamine käib stiilis:

PHP kood:
 
$queryBuilder 
= & new SQL_QueryBuilder();
        
$queryBuilder->useAliases(true);
        
$queryBuilder->resolveReferences(false);
        
$queryBuilder->addFieldRangeOfType('messageentity');
        
$queryBuilder->addField(new SQLField('VID''COUNT''sentmessage''MessageCount'));
        
$queryBuilder->addEntityBackReference(new EntityBackReference( new MessageEntity(), new SentmessageEntity(), SQL_JOINTYPE_LEFT));
        
$queryBuilder->addFilterRange($sqlFilterCollection);
        
$queryBuilder->addSortingRule(new SQL_SortingRule(new SQLField('message_Timestamp'), ' DESC'));
        
$queryBuilder->addGroupByField(new SQLField('VID'null'message'));
        
$queryBuilder->sqlLimit($pageSize$offset);
        
$queryBuilder->SQLTable(new SQLTable('message'));
        
$queryBuilder->baseType('messageentity');
        
$dataTable = & new DataTable();
        
$dataTable->fill($queryBuilder);


nagu ta mul umbes käib, siis kipub välju tõesti väga kergesti paljuks minema mõnikord. Näiteks kui seal kirjutada
$queryBuilder->resolveReferences(true);
siis võetakse JOINidega külge kõik antud tabeliga seotud tabelid ja lisaks võib külge võtta kõigi nende tabelite väljad. Mõnikord on vaja ja on kiirem ühe sellise suure päringuga kõik vajalik kätte saada, kui käia sadu väikseid päringuid tegemas. Loomulikult on optimeerimisruumi, sest see querybuilder kui selline sai suht eile alles tehtud.

Asja uba on selles, et ei peaks SQL-ga tegelema seal kus pole vaja. Aga nagu näha - praegu on vaja.

Monte - indekseerisin ja möllasin selle päringuga, aga aeglaseks ta jäi ikkagi. Võtsin ära GROUP by ja selle COUNT välja ja küsin iga rea kohta eraldi seda sentmessagecounti. Kuna asi käib lehekülgede kaupa ja igal lehel ca 50 rida, siis käib hetkel asi talutava kiirusega (testläpakas, kus ma arendan). Hiljem tegelen edasi sellega.

RE: MySQL päringute optimeerimine
Postitaja: aksel 2006-03-09 16:42:13
TSITEERITUD:


QueryBuilder ei tähenda, et kohe peab miski rõve päringujurakas tulema ... Loomulikult on QueryBuilder hää asi ja see ei ole vaidluse objekt.


Ma võin oma querybuilderiga teha ka "SELECT * FROM pisiketabel", aga selle jaoks ei ole vaja ehitada querybuildereid:)
RE: MySQL päringute optimeerimine
Postitaja: ninnunännu 2006-03-09 16:57:54
TSITEERITUD:


Ma võin oma querybuilderiga teha ka "SELECT * FROM pisiketabel", aga selle jaoks ei ole vaja ehitada querybuildereid:)
Okei, see läheb nüüd jah tiba teise äärmusesse.
QueryBuilder-ite probleem ongi tihti just selles, millele aksel viitab: mingid genereeritud päringud ei tööta normaalse kiirusega. Ja siis oleks hea, kui suudaks päringut lahata inimlikult. Selles on point ...
RE: MySQL päringute optimeerimine
Postitaja: scorps 2006-03-09 17:05:02
TSITEERITUD:

Ahjaa, scorps, mis lahmimisse puutub. Sa pole siiani näidanud ühtegi suuremat vidinat, mis pilte DB-s hoiaks ja samal aja kiire oleks ... et siis mina lahmin ... :p

aga lahmid. ma tõin suure hunniku näited ja põhjendusi miks data baasis on parem. Sina lahmisid aint, et teistpidi on parem toomata mingeid argumente. Ma ei tea pea sulle hetkel midagi tõestama.
RE: MySQL päringute optimeerimine
Postitaja: scorps 2006-03-09 17:07:42
TSITEERITUD:
Okei, see läheb nüüd jah tiba teise äärmusesse.
QueryBuilder-ite probleem ongi tihti just selles, millele aksel viitab: mingid genereeritud päringud ei tööta normaalse kiirusega. Ja siis oleks hea, kui suudaks päringut lahata inimlikult. Selles on point ...

saadki lahata seda ilma probleemideta, võtta builderist lõppppäring välja ja siis seda näiteks phpmyadminis analüüsida? kus su point ikkagi on? ka handcraft-päringud ei tööta alati õieti.
RE: MySQL päringute optimeerimine
Postitaja: aksel 2006-03-09 20:38:13
TSITEERITUD:
Okei, see läheb nüüd jah tiba teise äärmusesse.
QueryBuilder-ite probleem ongi tihti just selles, millele aksel viitab: mingid genereeritud päringud ei tööta normaalse kiirusega. Ja siis oleks hea, kui suudaks päringut lahata inimlikult. Selles on point ...


Said veits valesti aru - ma võiks täpselt samasuguse päringu kirjutada käsitsi ka, ikka töötaks ta aeglaselt, sest group by ja order põhjustavad selle, et mysql teeb kettale ajutise tabeli jne. Võib-olla pean veel indeksitega mässama, eks näis. Homme.
RE: MySQL päringute optimeerimine
Postitaja: aksel 2006-03-09 20:39:50
TSITEERITUD:

saadki lahata seda ilma probleemideta, võtta builderist lõppppäring välja ja siis seda näiteks phpmyadminis analüüsida? kus su point ikkagi on? ka handcraft-päringud ei tööta alati õieti.


Väike kommentaar siia - SQLYog on selle koha pealt ülikõva. No seal on väga mugav arendada päringuid ja värke. Meenutad kergelt MS SQL serveriga kaasaskäivat query analyzerit, mis on ka väga hea tööriist.
RE: MySQL päringute optimeerimine
Postitaja: aju 2006-03-10 00:23:45
TSITEERITUD:


Kommenteeri palun täpsemalt, mis tal viga on. Siis teab ehk parandada;)



Proovi lahti saada sellest:
WHERE 1=1

ja päring peaks toimima tunduvalt kiiremini


RE: MySQL päringute optimeerimine
Postitaja: aksel 2006-03-10 09:06:56
TSITEERITUD:


Proovi lahti saada sellest:
WHERE 1=1

ja päring peaks toimima tunduvalt kiiremini




See ei muuda midagi. See on seal querybuilderi jaoks, mis siis lisab sinna otsa lihtsat AND või OR filtreid, aga see ei muuda päris kindlasti mitte midagi:)

RE: MySQL päringute optimeerimine
Postitaja: oh oinas oinake 2006-03-10 09:12:50
lase või oracle execution planist läbi koos 1=1 ja ilma...vaata mis indeksite kasuks ostustatakse ja mis cost tuleb
RE: MySQL päringute optimeerimine
Postitaja: aksel 2006-03-10 09:58:05
TSITEERITUD:
lase või oracle execution planist läbi koos 1=1 ja ilma...vaata mis indeksite kasuks ostustatakse ja mis cost tuleb


Ma ei tea, mis Oracle sellest arvab, aga MySQL puhul EXPLAIN SELECT tulemus on täpselt sama.

Kurja juur antud päringus on LEFT JOIN'i, GROUP BY ja ORDER BY kombinatsioon.

Tegelen sellega just parasjagu.

Ja ilmselt leidsin just ka lahenduse - kasutan ära MySQL'i omadust sortida tulemust GROUP BY järgi. St et juba GROUP BY juures saab määrata, kas sortida tulemus ASC või DESC.
RE: MySQL päringute optimeerimine
Postitaja: votsiis 2006-03-10 10:18:36
TSITEERITUD:

Kurja juur antud päringus on LEFT JOIN'i, GROUP BY ja ORDER BY kombinatsioon.


Soovitan katsetada ka RIGHT JOINi.
Lisaks sellele on minuarust oluline, mis järjekorras sa tabelid teineteisega JOINid, st kumb kummale külge haagitakse. Manuaalis oli päringute ja indeksi optimeerimise all sellest nagu juttu kunagi, ei viitsi otsida.

Leheküljed: 1 2 

©2002-2013 Martin Rebane & PHP.ee kaasautorid
  0.0918951034546