Project

General

Profile

Actions
Copy link

Bug #2747

open

very slow SQL query in CTF results page

Added by Anonymous over 10 years ago. Updated over 10 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Category:
-
Target version:
-
Start date:
04/28/2014
Due date:
% Done:

0%

Estimated time:
Affected Version:
Appion/Leginon 2.2.0
Show in known bugs:
No
Workaround:

Description

There is a query in the CTF results page that is giving me problems. It is extremely slow, and not only that, seems to lock the AcquisitionImageData table which can block data collection.

SELECT ctfdata.* 
FROM ( SELECT i.`filename`, 
              i.`DEF_id` AS imageid, 
              p.`name`, 
              s.`defocus`, 
              unix_timestamp(i.`DEF_timestamp`) as unix_timestamp, 
              2*ABS(ctf.defocus1-ctf.defocus2)/(ctf.defocus1+ctf.defocus2) AS difference, 
              ctf.* 
       FROM `ApCtfData` AS ctf 
       LEFT JOIN leginondb.`AcquisitionImageData` AS i ON (i.`DEF_id` = ctf.`REF|leginondata|AcquisitionImageData|image`) 
       LEFT JOIN leginondb.`PresetData` AS p ON (p.DEF_id = i.`REF|PresetData|preset`) 
       LEFT JOIN leginondb.`ScopeEMData` AS s ON (i.`REF|ScopeEMData|scope` = s.`DEF_id`) 
       LEFT JOIN leginondb.`ViewerImageStatus` AS viewer ON viewer.`REF|AcquisitionImageData|image` = ctf.`REF|leginondata|AcquisitionImageData|image` 
       WHERE ctf.`REF|leginondata|AcquisitionImageData|image` IN ( SELECT a.`DEF_id` 
                                                                                                         FROM leginondb.`AcquisitionImageData` AS a 
                                                                                                         WHERE a.`REF|SessionData|session`='6395' 
                                                                                                         AND ABS(s.`SUBD|stage position|a`) < 0.9 
                                                                                                         AND ( viewer.status IS NULL OR viewer.status != 'hidden' ) ) 
       ORDER BY coalesce(resolution_80_percent,999) ASC , 
                       coalesce(resolution_50_percent,999) ASC, 
                       coalesce(ctf.`cross_correlation`,(ctf.`confidence`*ctf.`confidence_d`),999) DESC ) ctfdata 
 WHERE ctfdata.confidence > 0.2 || ctfdata.confidence_d > 0.2 
 GROUP BY ctfdata.imageid 
 ORDER BY ctfdata.unix_timestamp
Actions
Copy link

Also available in: Atom PDF