Project

General

Profile

Bug #7940

Updated by Neil Voss over 5 years ago

General MySQL MariaDB speed ups, from the slow query log 

 Bad Queries: 

 <pre> 
 SELECT `AcquisitionImageData16916320`.*    FROM `ny_leginondb`.`AcquisitionImageData` AS `AcquisitionImageData16916320`    JOIN (`ny_leginondb`.`CameraEMData` AS `CameraEMData16488768`, `ny_leginondb`.`InstrumentData` AS `InstrumentData16472928`) ON (`AcquisitionImageData16916320`.`REF|CameraEMData|camera` = `CameraEMData16488768`.`DEF_ID` AND `CameraEMData16488768`.`REF|InstrumentData|ccdcamera` = `InstrumentData16472928`.`DEF_ID`) WHERE `AcquisitionImageData16916320`.`DEF_timestamp` >= DATE_ADD(now(), INTERVAL '-30 0:0:0' DAY_SECOND)    AND    `CameraEMData16488768`.`frames name`="20190212_17030045"    AND    `InstrumentData16472928`.`hostname`="krios1-k2"    ORDER BY `AcquisitionImageData16916320`.DEF_id DESC; 

 SELECT `ApStackParticleData23462240`.*    FROM `nyap_31`.`ApStackParticleData` AS `ApStackParticleData23462240`     WHERE    `ApStackParticleData23462240`.`REF|ApStackData|stack`="1164"    ORDER BY `ApStackParticleData23462240`.DEF_id DESC; 

 SELECT `CameraEMData16888160`.*    FROM `ny_leginondb`.`AcquisitionImageData` AS `AcquisitionImageData16488768`    JOIN (`ny_leginondb`.`CameraEMData` AS `CameraEMData16888160`, `ny_leginondb`.`InstrumentData` AS `InstrumentData16002720`) ON (`AcquisitionImageData16488768`.`REF|CameraEMData|camera` = `CameraEMData16888160`.`DEF_ID` AND `CameraEMData16888160`.`REF|InstrumentData|ccdcamera` = `InstrumentData16002720`.`DEF_ID`) WHERE `AcquisitionImageData16488768`.`DEF_timestamp` >= DATE_ADD(now(), INTERVAL '-30 0:0:0' DAY_SECOND)    AND    `CameraEMData16888160`.`frames name`="20190212_17022944"    AND    `InstrumentData16002720`.`hostname`="krios1-k2"    ORDER BY `AcquisitionImageData16488768`.DEF_id DESC; 


 SELECT DISTINCT im.`DEF_id` AS imgid FROM ApStackParticleData AS stpart LEFT JOIN ApParticleData AS part ON stpart.`REF|ApParticleData|particle` = part.`DEF_id` LEFT JOIN ny_leginondb.`AcquisitionImageData` AS im ON part.`REF|leginondata|AcquisitionImageData|image` = im.`DEF_id` WHERE `REF|ApStackData|stack` = '1169'; 

 SELECT `InstrumentData16876624`.*    FROM `ny_leginondb`.`AcquisitionImageData` AS `AcquisitionImageData15786176`    JOIN (`ny_leginondb`.`CameraEMData` AS `CameraEMData16458768`, `ny_leginondb`.`InstrumentData` AS `InstrumentData16876624`) ON (`AcquisitionImageData15786176`.`REF|CameraEMData|camera` = `CameraEMData16458768`.`DEF_ID` AND `CameraEMData16458768`.`REF|InstrumentData|ccdcamera` = `InstrumentData16876624`.`DEF_ID`) WHERE `AcquisitionImageData15786176`.`DEF_timestamp` >= DATE_ADD(now(), INTERVAL '-30 0:0:0' DAY_SECOND)    AND    `CameraEMData16458768`.`frames name`="ReferenceBuffers"    AND    `InstrumentData16876624`.`hostname`="tf20-de20"    ORDER BY `AcquisitionImageData15786176`.DEF_id DESC; 

 SELECT stackpart.mean AS mean, stackpart.stdev AS stdev FROM `ApStackParticleData` AS stackpart WHERE `REF|ApStackData|stack` = '1164'; 

 </pre> 

 Fixed: 
 <pre> 
 SELECT UNIX_TIMESTAMP(img.`DEF_timestamp`) AS `start` FROM `AcquisitionImageData` AS img WHERE `REF|SessionData|session` = '9593' ORDER BY img.`DEF_timestamp` ASC LIMIT 1; 
 SELECT UNIX_TIMESTAMP(img.`DEF_timestamp`) AS `end` FROM `AcquisitionImageData` AS img WHERE `REF|SessionData|session` = '8352' ORDER BY img.`DEF_timestamp` DESC LIMIT 1; 
 </pre>

Back