Appion

General MySQL MariaDB speed ups, from the slow query log

Bad Queries:

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';

Fixed:

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;