в общем добавил я пару параметров в конф
long_query_time = 5
log-slow-queries=/var/log/mysql.log
c программы клиента делаю запрос в базу с поиском позиции 00000166. жду сек 10
[root@servak1 ~]# cat /var/log/mysql.log
# Time: 120305 10:27:09
# User@Host: user[] @ [192.168.1.227]
# Query_time: 6.658888 Lock_time: 0.002088 Rows_sent: 0 Rows_examined: 36354758
use rp78;
SET timestamp=1330921629;
SELECT `DetCode`, `HeaderCode`, `CorrAcc`, `summrecs`.`OurLogNumber`, `summrecs`.`ManRefNumber`, `summrecs`.`EANCode`, `IsUniqueName`, `UniqueName`, `NonTypicalPlaced`, `PlaceCode`, `History` AS MHistory, `Remark` AS MRemark, `DateOfWrite`, `IsUsedAsBase`, `DateOfUsing`, `TypeOfRecord`, `RoomCode`, `TypeOfOriginRec`, `Price`,`ExtUniqueName`, SUM( `QtyOnOrigin` ) AS StartQty, SUM( `QtyIn` ) AS PlusQty, SUM( `QtyOut` ) AS MinusQty, SUM( `QtyOnFinish` ) AS FinishQty, SUM(`SummPerString`) AS SumPerItems,`UniqueUnitPerPack`, IF(`IsUniqueName`,`UniqueName`,`detmatregistry`.`RussianName`) As `Name`, IF(`IsUniqueName`,`UniqueUnitPerPack`,`detmatregistry`.`UnitPerPack`) As `UnitPerPack` FROM `summrecs` left join (`iecommon`.`detmatregistry`) ON (`summrecs`.`ManRefNumber`=`detmatregistry`.`ManRefNumber`) WHERE (`summrecs`.`ManRefNumber` IN ( SELECT `ManRefNumber` FROM `iecommon`.`sinview` WHERE (`ManRefNumber` IS NOT NULL) AND (`SinManRefNumber`='00000166' OR `SinName`='��������������� ��-2-35� 1,5��') ))OR( `UniqueName` IN ( SELECT `RussianName` AS `UniqueName` FROM `iecommon`.`sinview` WHERE (`RussianName` IS NOT NULL) AND (`SinManRefNumber`='00000166' OR `SinName`='��������������� ��-2-35� 1,5��') )) GROUP BY `ManRefNumber`,`IsUniqueName`,`UniqueName`,`NonTypicalPlaced`,`PlaceCode` ORDER BY `ManRefNumber` ASC;
Затем делаю еще один запрос, который длится сек 20.
[root@servak1 ~]# cat /var/log/mysql.log
# Time: 120305 10:31:43
# User@Host: user[] @ [192.168.1.227]
# Query_time: 6.402292 Lock_time: 0.000379 Rows_sent: 0 Rows_examined: 36354758
SET timestamp=1330921903;
SELECT `DetCode`, `HeaderCode`, `CorrAcc`, `summrecs`.`OurLogNumber`, `summrecs`.`ManRefNumber`, `summrecs`.`EANCode`, `IsUniqueName`, `UniqueName`, `NonTypicalPlaced`, `PlaceCode`, `History` AS MHistory, `Remark` AS MRemark, `DateOfWrite`, `IsUsedAsBase`, `DateOfUsing`, `TypeOfRecord`, `RoomCode`, `TypeOfOriginRec`, `Price`,`ExtUniqueName`, SUM( `QtyOnOrigin` ) AS StartQty, SUM( `QtyIn` ) AS PlusQty, SUM( `QtyOut` ) AS MinusQty, SUM( `QtyOnFinish` ) AS FinishQty, SUM(`SummPerString`) AS SumPerItems,`UniqueUnitPerPack`, IF(`IsUniqueName`,`UniqueName`,`detmatregistry`.`RussianName`) As `Name`, IF(`IsUniqueName`,`UniqueUnitPerPack`,`detmatregistry`.`UnitPerPack`) As `UnitPerPack` FROM `summrecs` left join (`iecommon`.`detmatregistry`) ON (`summrecs`.`ManRefNumber`=`detmatregistry`.`ManRefNumber`) WHERE (`summrecs`.`ManRefNumber` IN ( SELECT `ManRefNumber` FROM `iecommon`.`sinview` WHERE (`ManRefNumber` IS NOT NULL) AND (`SinManRefNumber`='00000166' OR `SinName`='��������������� ��-2-35� 1,5��') ))OR( `UniqueName` IN ( SELECT `RussianName` AS `UniqueName` FROM `iecommon`.`sinview` WHERE (`RussianName` IS NOT NULL) AND (`SinManRefNumber`='00000166' OR `SinName`='��������������� ��-2-35� 1,5��') )) GROUP BY `ManRefNumber`,`IsUniqueName`,`UniqueName`,`NonTypicalPlaced`,`PlaceCode` ORDER BY `ManRefNumber` ASC;
# Time: 120305 10:31:50
# User@Host: user[] @ [192.168.1.227]
# Query_time: 6.391286 Lock_time: 0.000381 Rows_sent: 0 Rows_examined: 36354758
SET timestamp=1330921910;
SELECT `DetCode`, `HeaderCode`, `CorrAcc`, `summrecs`.`OurLogNumber`, `summrecs`.`ManRefNumber`, `summrecs`.`EANCode`, `IsUniqueName`, `UniqueName`, `NonTypicalPlaced`, `PlaceCode`, `History` AS MHistory, `Remark` AS MRemark, `DateOfWrite`, `IsUsedAsBase`, `DateOfUsing`, `TypeOfRecord`, `RoomCode`, `TypeOfOriginRec`, `Price`,`ExtUniqueName`, SUM( `QtyOnOrigin` ) AS StartQty, SUM( `QtyIn` ) AS PlusQty, SUM( `QtyOut` ) AS MinusQty, SUM( `QtyOnFinish` ) AS FinishQty, SUM(`SummPerString`) AS SumPerItems,`UniqueUnitPerPack`, IF(`IsUniqueName`,`UniqueName`,`detmatregistry`.`RussianName`) As `Name`, IF(`IsUniqueName`,`UniqueUnitPerPack`,`detmatregistry`.`UnitPerPack`) As `UnitPerPack` FROM `summrecs` left join (`iecommon`.`detmatregistry`) ON (`summrecs`.`ManRefNumber`=`detmatregistry`.`ManRefNumber`) WHERE (`summrecs`.`ManRefNumber` IN ( SELECT `ManRefNumber` FROM `iecommon`.`sinview` WHERE (`ManRefNumber` IS NOT NULL) AND (`SinManRefNumber`='00000166' OR `SinName`='��������������� ��-2-35� 1,5��') ))OR( `UniqueName` IN ( SELECT `RussianName` AS `UniqueName` FROM `iecommon`.`sinview` WHERE (`RussianName` IS NOT NULL) AND (`SinManRefNumber`='00000166' OR `SinName`='��������������� ��-2-35� 1,5��') )) GROUP BY `ManRefNumber`,`IsUniqueName`,`UniqueName`,`NonTypicalPlaced`,`PlaceCode` ORDER BY `ManRefNumber` ASC;
# Time: 120305 10:31:56
# User@Host: user[] @ [192.168.1.227]
# Query_time: 6.398156 Lock_time: 0.000455 Rows_sent: 0 Rows_examined: 36354758
SET timestamp=1330921916;
SELECT `DetCode`, `HeaderCode`, `CorrAcc`, `summrecs`.`OurLogNumber`, `summrecs`.`ManRefNumber`, `summrecs`.`EANCode`, `IsUniqueName`, `UniqueName`, `NonTypicalPlaced`, `PlaceCode`, `History` AS MHistory, `Remark` AS MRemark, `DateOfWrite`, `IsUsedAsBase`, `DateOfUsing`, `TypeOfRecord`, `RoomCode`, `TypeOfOriginRec`, `Price`,`ExtUniqueName`, SUM( `QtyOnOrigin` ) AS StartQty, SUM( `QtyIn` ) AS PlusQty, SUM( `QtyOut` ) AS MinusQty, SUM( `QtyOnFinish` ) AS FinishQty, SUM(`SummPerString`) AS SumPerItems,`UniqueUnitPerPack`, IF(`IsUniqueName`,`UniqueName`,`detmatregistry`.`RussianName`) As `Name`, IF(`IsUniqueName`,`UniqueUnitPerPack`,`detmatregistry`.`UnitPerPack`) As `UnitPerPack` FROM `summrecs` left join (`iecommon`.`detmatregistry`) ON (`summrecs`.`ManRefNumber`=`detmatregistry`.`ManRefNumber`) WHERE (`summrecs`.`ManRefNumber` IN ( SELECT `ManRefNumber` FROM `iecommon`.`sinview` WHERE (`ManRefNumber` IS NOT NULL) AND (`SinManRefNumber`='00000166' OR `SinName`='��������������� ��-2-35� 1,5��') ))OR( `UniqueName` IN ( SELECT `RussianName` AS `UniqueName` FROM `iecommon`.`sinview` WHERE (`RussianName` IS NOT NULL) AND (`SinManRefNumber`='00000166' OR `SinName`='��������������� ��-2-35� 1,5��') )) GROUP BY `ManRefNumber`,`IsUniqueName`,`UniqueName`,`NonTypicalPlaced`,`PlaceCode` ORDER BY `ManRefNumber` ASC;
я вот в последнем логе не понял почему запрос выполняется 3 раза?
Данные по ходу вытягиваются каждый раз
