CODEWITHSUNDEEP
phpmysql
Saef Myth
Saef Myth

Reputation: 143

shorting mysql query with more performance

How can I shorten this query?

SET sql_mode = '';
SELECT t1.`id_number`,t1.`parents_id_number`,t1.`student_first_name`, 
    SUM(t1.`degree_value`) AS `values`,
    (SELECT `user_key`  
        FROM data_users.account_info t2 
        WHERE t1.id_number = t2.id_number AND t1.parents_id_number = t2.parents_id_number 
        LIMIT 1) AS `user_key`,
    (SELECT `first_name` 
        FROM data_users.account_info t2 
        WHERE t1.id_number = t2.id_number AND t1.parents_id_number = t2.parents_id_number 
        LIMIT 1) AS `first_name`,
    (SELECT `last_name` 
        FROM data_users.account_info t2 
        WHERE t1.id_number = t2.id_number AND t1.parents_id_number = t2.parents_id_number 
        LIMIT 1) AS `last_name`,
    (SELECT `about_me`  
        FROM data_users.account_info t2 
        WHERE t1.id_number = t2.id_number AND t1.parents_id_number = t2.parents_id_number 
        LIMIT 1) AS `about_me`
FROM `1479213957519` t1     
WHERE t1.year_half = '1' 
GROUP BY t1.`perfix` 
ORDER BY `Values` DESC 
LIMIT 6

Upvotes: 0

Views: 230

Answers (1)

Barmar
Barmar

Reputation: 781974

Use a join instead of correlated subqueries.

SELECT t1.`id_number`,t1.`parents_id_number`,t1.`student_first_name`, 
        SUM(t1.`degree_value`) AS `values`,
        t2.user_key, t2.first_name, t2.last_name, t2.about_me
FROM `1479213957519` t1
JOIN (SELECT *
      FROM data_users.account_info 
      GROUP BY id_number, parents_id_number) t2 
    ON t1.id_number = t2.id_number AND t1.parents_id_number = t2.parents_id_number
WHERE t1.year_half = '1' 
GROUP BY t1.`perfix` 
ORDER BY `Values` DESC 
LIMIT 6

If id_number and parents_id_number uniquely identifies a row in account_info, you don't need the subquery, you can just join with the table itself.

SELECT t1.`id_number`,t1.`parents_id_number`,t1.`student_first_name`, 
        SUM(t1.`degree_value`) AS `values`,
        t2.user_key, t2.first_name, t2.last_name, t2.about_me
FROM `1479213957519` t1
JOIN data_users.account_info  t2 
    ON t1.id_number = t2.id_number AND t1.parents_id_number = t2.parents_id_number
WHERE t1.year_half = '1' 
GROUP BY t1.`perfix` 
ORDER BY `Values` DESC 
LIMIT 6

Upvotes: 3

Related Questions