3
\$\begingroup\$

I'm replicating a scenario on a sample set for better understanding.

Database Table: Products DB Table

Report required from that table,

enter image description here

the query is working fine, and I'm able to get the required result. But, I believe it can be optimized. So, looking for help to improve it. Any help in simplifying this one would be great!

Note: Customer name field numbers are very random, there is absolutely no trend. So, that messed up sequence is done purposely..

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_22_1`,' ',APF.`element_22_2`) AS 'Provider Name',
    APF.`element_29` AS 'Provider Email',
    APF.`element_27` AS 'Provider NPI',
    APF.`element_34` AS 'Provider Medical License Number',
    APEO_PC.`option` AS 'Provider Credentails', 
    APEO_PS.`option` AS 'Provider Specialty',
    APF.`element_141` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` APF
INNER JOIN  `PaymentPortal`.`Invoices` PPI ON  (APF.`id` =  PPI.`SessionID`)
LEFT JOIN `Forms`.`ap_element_options` APEO_PC ON (APF.`element_25` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='25')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >0 AND APF.`element_175` <11

UNION ALL 

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_30_1`,' ',APF.`element_30_2`) AS 'Provider Name',
    APF.`element_35` AS 'Provider Email',
    APF.`element_33` AS 'Provider NPI',
    APF.`element_28` AS 'Provider Medical License Number',
    APF.`element_45` AS 'Provider Credentials',
    APEO_PC.`option` AS 'Provider Credentails 1',   
    APF.`element_172` AS 'Provider Specialty',
    APF.`element_139` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` 
INNER JOIN  `PaymentPortal`.`Invoices` ON  APF.`id` =  PPI.`SessionID` 
LEFT JOIN `Forms`.`ap_element_options` ON (APF.`element_45` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='45')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >1 AND APF.`element_175` <11

UNION ALL 

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_42_1`,' ',APF.`element_42_2`) AS 'Provider Name',
    APF.`element_76` AS 'Provider Email',
    APF.`element_61` AS 'Provider NPI',
    APF.`element_68` AS 'Provider Medical License Number',
    APF.`element_31` AS 'Provider Credentials',
    APEO_PC.`option` AS 'Provider Credentails 1',   
    APF.`element_171` AS 'Provider Specialty',
    APF.`element_137` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` 
INNER JOIN  `PaymentPortal`.`Invoices` ON  APF.`id` =  PPI.`SessionID` 
LEFT JOIN `Forms`.`ap_element_options` ON (APF.`element_31` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='31')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >2 AND APF.`element_175` <11


UNION ALL 

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_41_1`,' ',APF.`element_41_2`) AS 'Provider Name',
    APF.`element_77` AS 'Provider Email',
    APF.`element_60` AS 'Provider NPI',
    APF.`element_69` AS 'Provider Medical License Number',
    APF.`element_48` AS 'Provider Credentials',
    APEO_PC.`option` AS 'Provider Credentails 1',   
    APF.`element_170` AS 'Provider Specialty',
    APF.`element_135` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` 
INNER JOIN  `PaymentPortal`.`Invoices` ON  APF.`id` =  PPI.`SessionID` 
LEFT JOIN `Forms`.`ap_element_options` ON (APF.`element_48` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='48')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >3 AND APF.`element_175` <11


UNION ALL 

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_43_1`,' ',APF.`element_43_2`) AS 'Provider Name',
    APF.`element_78` AS 'Provider Email',
    APF.`element_62` AS 'Provider NPI',
    APF.`element_70` AS 'Provider Medical License Number',
    APF.`element_47` AS 'Provider Credentials',
    APEO_PC.`option` AS 'Provider Credentails 1',   
    APF.`element_169` AS 'Provider Specialty',
    APF.`element_133` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` 
INNER JOIN  `PaymentPortal`.`Invoices` ON  APF.`id` =  PPI.`SessionID` 
LEFT JOIN `Forms`.`ap_element_options` ON (APF.`element_47` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='47')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >4 AND APF.`element_175` <11


UNION ALL 

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_40_1`,' ',APF.`element_40_2`) AS 'Provider Name',
    APF.`element_79` AS 'Provider Email',
    APF.`element_63` AS 'Provider NPI',
    APF.`element_73` AS 'Provider Medical License Number',
    APF.`element_46` AS 'Provider Credentials',
    APEO_PC.`option` AS 'Provider Credentails 1',   
    APF.`element_168` AS 'Provider Specialty',
    APF.`element_131` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` 
INNER JOIN  `PaymentPortal`.`Invoices` ON  APF.`id` =  PPI.`SessionID` 
LEFT JOIN `Forms`.`ap_element_options` ON (APF.`element_46` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='46')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >5 AND APF.`element_175` <11


UNION ALL 

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_37_1`,' ',APF.`element_37_2`) AS 'Provider Name',
    APF.`element_80` AS 'Provider Email',
    APF.`element_64` AS 'Provider NPI',
    APF.`element_71` AS 'Provider Medical License Number',
    APF.`element_44` AS 'Provider Credentials',
    APEO_PC.`option` AS 'Provider Credentails 1',   
    APF.`element_167` AS 'Provider Specialty',
    APF.`element_129` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` 
INNER JOIN  `PaymentPortal`.`Invoices` ON  APF.`id` =  PPI.`SessionID` 
LEFT JOIN `Forms`.`ap_element_options` ON (APF.`element_44` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='44')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >6 AND APF.`element_175` <11

UNION ALL 

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_39_1`,' ',APF.`element_39_2`) AS 'Provider Name',
    APF.`element_81` AS 'Provider Email',
    APF.`element_66` AS 'Provider NPI',
    APF.`element_72` AS 'Provider Medical License Number',
    APF.`element_57` AS 'Provider Credentials',
    APEO_PC.`option` AS 'Provider Credentails 1',   
    APF.`element_166` AS 'Provider Specialty',
    APF.`element_127` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` 
INNER JOIN  `PaymentPortal`.`Invoices` ON  APF.`id` =  PPI.`SessionID` 
LEFT JOIN `Forms`.`ap_element_options` ON (APF.`element_57` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='57')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >7 AND APF.`element_175` <11

UNION ALL 

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_36_1`,' ',APF.`element_36_2`) AS 'Provider Name',
    APF.`element_82` AS 'Provider Email',
    APF.`element_65` AS 'Provider NPI',
    APF.`element_74` AS 'Provider Medical License Number',
    APF.`element_58` AS 'Provider Credentials',
    APEO_PC.`option` AS 'Provider Credentails 1',   
    APF.`element_165` AS 'Provider Specialty',
    APF.`element_126` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` 
INNER JOIN  `PaymentPortal`.`Invoices` ON  APF.`id` =  PPI.`SessionID` 
LEFT JOIN `Forms`.`ap_element_options` ON (APF.`element_58` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='58')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >8 AND APF.`element_175` <11

UNION ALL 

SELECT APF.`id` AS 'Id', 
    APF.`element_1` AS 'Practice Name',
    APF.`element_118` AS 'Federal Tax ID', 
    APF.`element_2` AS 'Are you currently receiving Meaningful Use assistance from NJ-HITEC?', 
    CONCAT(APF.`element_3_1`,' ',APF.`element_3_2`,' ',APF.`element_3_3`,' ',APF.`element_3_4`,' ',APF.`element_3_5`,' ',APF.`element_3_6`) AS 'Primary Site Address', 
    APF.`element_4` AS 'Phone', 
    APF.`element_5` AS 'Fax', 
    APF.`element_153` AS 'Have you reassigned your practices billing rights to a Medicare Accountable Care Organization (ACO) this year?', 
    CONCAT(APF.`element_87_1`,' ',APF.`element_87_2`) AS 'Name', 
    APF.`element_88` AS 'Email', 
    APF.`element_89` AS 'Phone', 
    APF.`element_175` AS 'Number of providers at practice',  
    CONCAT(APF.`element_38_1`,' ',APF.`element_38_2`) AS 'Provider Name',
    APF.`element_83` AS 'Provider Email',
    APF.`element_59` AS 'Provider NPI',
    APF.`element_75` AS 'Provider Medical License Number',
    APF.`element_67` AS 'Provider Credentials',
    APEO_PC.`option` AS 'Provider Credentails 1',   
    APF.`element_164` AS 'Provider Specialty',
    APF.`element_173` AS 'Other',   
    PPI.`Status`,  
    PPI.`PaymentDate`
FROM  `Forms`.`ap_form_51835` 
INNER JOIN  `PaymentPortal`.`Invoices` ON  APF.`id` =  PPI.`SessionID` 
LEFT JOIN `Forms`.`ap_element_options` ON (APF.`element_67` = APEO_PC.`option_id` AND APEO_PC.`form_id`='51835' AND APEO_PC.`element_id`='67')
WHERE  PPI.`Status` =  'Payment Successful' 
    AND YEAR( STR_TO_DATE(PPI.`PaymentDate` ,  "%m/%d/%Y" ) ) =  '2016'
    AND APF.`element_20` IS NULL 
    AND APF.`element_84` IS NULL
    AND APF.`element_175` >9 AND APF.`element_175` <11
\$\endgroup\$

closed as unclear what you're asking by Jamal Mar 10 '18 at 21:43

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • \$\begingroup\$ Are you allowed to post the original query? Reviewing pseudo-code often won't find out the actual issue (and is also off-topic on Code Review) \$\endgroup\$ – Phrancis Jan 22 '16 at 16:35
  • \$\begingroup\$ @Phrancis, done.. \$\endgroup\$ – Maham Fatima Jan 22 '16 at 16:49
  • \$\begingroup\$ I've edited out the examplish pseudo-query, feel free to edit your title to concisely describe what your query is actually doing, as stated in the watermark: "State the task that yuor code accomplishes. Make your title distinctive." \$\endgroup\$ – Mathieu Guindon Jan 22 '16 at 16:52
  • \$\begingroup\$ As we all want to make our code more efficient or improve it in one way or another, try to write a title that summarizes what your code does, not what you want to get out of a review. \$\endgroup\$ – Jamal Jan 22 '16 at 16:52
1
\$\begingroup\$

Database design

Your query seems to be compensating for fundamental flaws in the database design. It's unfortunate that it has to come to that sometimes. If you don't have the means of making the changes, then sometimes UNION ALL is as good as it's going to get. The issue is for fields below this line:

APF.`element_175` AS 'Number of providers at practice',  

Below this in each query, the fields are all different, and that is probably causing a lot of calls on a lot of fields. You might consider using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; (reference) if dirty reads are acceptable in this query. But in either case, there is not really a good way to do something like this if the data is scattered among many different fields between queries.


Other than that, the query looks fine. You could extract some of the magic numbers to local variables, for example

WHERE [..] AND APF.`element_175` >1 AND APF.`element_175` <11

Could be something like:

SET @element75_min = 1;
SET @element75_max = 11;
[...]
WHERE [..] 
    AND APF.`element_175` > @element75_min 
    AND APF.`element_175` < @element75_max

Something like this would at least eliminate repeating the same numbers over and over, and if you had to change them, you only need to change the values in SET and not all over the script.


As far as more performance goes, there's really only so much that a SQL query has control over. You have to take into account indexes on tables/columns you are querying, and all sorts of other server-side variables that a DBA would know more about.

\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.