bradymiller wrote on Wednesday, February 06, 2013:
Yep,
Seeing no real difference in the query in my original commit vs your commit:
Current query mechanism
1345 rows in set (0.40 sec)
mysql> EXPLAIN SELECT sct_descriptions.ConceptId as code, sct_descriptions.Term as code_text ,c.id, c.code_type, c.modifier, c.units, c.fee, c.superbill, c.related_code, c.taxrates, c.cyp_factor, c.active, c.reportable, c.financial_reporting, ‘SNOMED-PR’ as code_type_name FROM sct_descriptions LEFT OUTER JOIN `codes` as c ON sct_descriptions.ConceptId = c.code AND c.code_type = ‘108’ INNER JOIN sct_concepts ON sct_descriptions.ConceptId=sct_concepts.ConceptId AND FullySpecifiedName like ‘%(procedure)’ WHERE sct_descriptions.Term LIKE ‘%biopsy%’ AND sct_descriptions.DescriptionStatus=0 AND sct_descriptions.DescriptionType=1 ORDER BY sct_descriptions.ConceptId+0,sct_descriptions.ConceptId;
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
| 1 | SIMPLE | sct_descriptions | ALL | NULL | NULL | NULL | NULL | 1177657 | Using where; Using filesort |
| 1 | SIMPLE | c | ref | code,code_type | code_type | 3 | const | 1 | |
| 1 | SIMPLE | sct_concepts | eq_ref | PRIMARY | PRIMARY | 8 | openemr.sct_descriptions.ConceptId | 1 | Using where |
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
3 rows in set (0.03 sec)
My original query mechanism
1345 rows in set (0.41 sec)
mysql> EXPLAIN SELECT sct_descriptions.ConceptId as code, sct_descriptions.Term as code_text ,c.id, c.code_type, c.modifier, c.units, c.fee, c.superbill, c.related_code, c.taxrates, c.cyp_factor, c.active, c.reportable, c.financial_reporting, ‘SNOMED-PR’ as code_type_name FROM sct_concepts LEFT OUTER JOIN `sct_descriptions` ON sct_concepts.ConceptId=sct_descriptions.ConceptId AND sct_concepts.ConceptStatus=0 AND sct_descriptions.DescriptionStatus=0 AND sct_descriptions.DescriptionType=1 LEFT OUTER JOIN `codes` as c ON sct_descriptions.ConceptId = c.code AND c.code_type = ‘108’ WHERE sct_concepts.FullySpecifiedName like ‘%(procedure)’ AND sct_descriptions.Term LIKE ‘%biopsy%’ ORDER BY sct_descriptions.ConceptId+0,sct_descriptions.ConceptId;
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
| 1 | SIMPLE | sct_descriptions | ALL | NULL | NULL | NULL | NULL | 1177657 | Using where; Using filesort |
| 1 | SIMPLE | sct_concepts | eq_ref | PRIMARY | PRIMARY | 8 | openemr.sct_descriptions.ConceptId | 1 | Using where |
| 1 | SIMPLE | c | ref | code,code_type | code_type | 3 | const | 1 | |
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
3 rows in set (0.00 sec)
Note I was able to reduce the scanned queries to about 400K in above EXPLAIN output is set an index in sct_descriptions.DescriptionStatus, however this didn’t seem to shorten the query times at all.
Here’s an error I am noting in Administration->Services if do:
1. Set the search to blank
2. Filter for ‘Diagnosis Reporting Only’ and/ord ‘Service Reporting Only’
3. Search SNOMED
(odd that the error only happens if there are no flagged ‘Diagnosis Reporting Only’ and/or ‘Service Reporting Only’ items)
ERROR: query failed: SELECT sct_descriptions.ConceptId as code, sct_descriptions.Term as code_text ,c.id, c.code_type, c.modifier, c.units, c.fee, c.superbill, c.related_code, c.taxrates, c.cyp_factor, c.active, c.reportable, c.financial_reporting, ‘SNOMED-CT’ as code_type_name FROM sct_descriptions LEFT OUTER JOIN `codes` as c ON sct_descriptions.ConceptId = c.code AND c.code_type = ? WHERE () AND sct_descriptions.DescriptionStatus=0 AND sct_descriptions.DescriptionType=3 AND c.financial_reporting=‘1’ ORDER BY sct_descriptions.ConceptId+0,sct_descriptions.ConceptId
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) AND sct_descriptions.DescriptionStatus=0 AND sct_descriptions.DescriptionType=’ at line 1
-brady
OpenEMR