please check my MYSQL query & give me advice?

Question

this is my mysql query.

SELECT s.s_nric                           AS nric,
       s.s_name                           AS name,
       s.s_psle_eng                       AS psle_eng,
       s.s_psle_math                      AS psle_maths,
       s.s_psle_aggr                      AS psle_aggr,
       (SELECT re.re_mark
        FROM   si_results re
        WHERE  re.re_code LIKE 'FEEN%'
               AND re.re_year = '2008'
               AND re.re_semester = '2'
               AND re.re_nric = s.s_nric) AS english_2008,
       (SELECT re.re_mark
        FROM   si_results re
        WHERE  re.re_code LIKE 'FEMA%'
               AND re.re_year = '2008'
               AND re.re_semester = '2'
               AND re.re_nric = s.s_nric) maths_2008,
       (SELECT re.re_mark
        FROM   si_results re
        WHERE  re.re_code LIKE 'FEEN%'
               AND re.re_year = '2009'
               AND re.re_semester = '2'
               AND re.re_nric = s.s_nric) AS english_2009,
       (SELECT re.re_mark
        FROM   si_results re
        WHERE  re.re_code LIKE 'FEMA%'
               AND re.re_year = '2009'
               AND re.re_semester = '2'
               AND re.re_nric = s.s_nric) maths_2009,
       isc.isc_g_gpa                      AS isc_gpa
FROM   si_student_data AS s
       LEFT JOIN si_isc_gpa AS isc
         ON isc.isc_g_nric = s.s_nric
WHERE  1 = 1
       AND s.s_admission_year = '2008'
GROUP  BY s.s_nric
ORDER  BY s.s_gender,
          s.s_name ASC 

please check my sub query

this is my sub query

(SELECT re.re_mark 
  FROM si_results re 
  WHERE re.re_code like 'FEEN%' 
    AND re.re_year='2008' 
    AND re.re_semester='2' 
    AND re.re_nric=s.s_nric) as English_2008, 
(SELECT re.re_mark 
  FROM si_results re 
  WHERE re.re_code like 'FEMA%' 
    AND re.re_year='2008' 
    AND re.re_semester='2' 
    AND re.re_nric=s.s_nric) Maths_2008, 
(SELECT re.re_mark 
  FROM si_results re 
  WHERE re.re_code like 'FEEN%' 
    AND re.re_year='2009' 
    AND re.re_semester='2' 
    AND re.re_nric=s.s_nric ) as English_2009, 
(SELECT re.re_mark 
  FROM si_results re 
  WHERE re.re_code like 'FEMA%' 
    AND re.re_year='2009' 
    AND re.re_semester='2' 
    AND re.re_nric=s.s_nric ) Maths_2009

When I execute my query, server take long time to execute. So how to make simple? please advice me.

Thanks.

Answer 1

Please, indent your code so we can read it and give you an answer.

Answer 2

It's important to know that doing a LIKE 'XXXX%' is very slow if it's not indexed. This can take forever in some cases. Also, you're doing four sub selects rather than joins which is again slower. Below I've added a full query which will produce the same results. The alternative to this is to reduce the size of the query by reducing the number of joins and instead have a NORMALIZED output. That is for every nric and name you'll have multiple rows with instead of re_mark for every type of class (english, maths, 2008, 2009, etc) you'll have two columns which contain the code (FEEN or FEMA) and the mark.

Give the following query a try and see if it works any better for you:

SELECT s.s_nric                           AS nric,
       s.s_name                           AS name,
       s.s_psle_eng                       AS psle_eng,
       s.s_psle_math                      AS psle_maths,
       s.s_psle_aggr                      AS psle_aggr,
       e_2008_feen.re_mark                AS english_2008,
       e_2008_fema.re_mark                AS maths_2008,
       e_2009_feen.re_mark                AS english_2009,
       e_2009_fema.re_mark                AS maths_2009,
       isc.isc_g_gpa                      AS isc_gpa
FROM   si_student_data AS s
INNER JOIN si_results e_2008_feen
    ON e_2008_feen.re_code LIKE 'FEEN%'
       AND e_2008_feen.re_year = '2008'
       AND e_2008_feen.re_semester = '2'
       AND e_2008_feen.re_nric = s.s_nric

INNER JOIN si_results e_2008_fema
    ON e_2008_fema.re_code LIKE 'FEMA%'
       AND e_2008_fema.re_year = '2008'
       AND e_2008_fema.re_semester = '2'
       AND e_2008_fema.re_nric = s.s_nric

INNER JOIN si_results e_2009_feen
    ON e_2009_feen.re_code LIKE 'FEEN%'
       AND e_2009_feen.re_year = '2009'
       AND e_2009_feen.re_semester = '2'
       AND e_2009_feen.re_nric = s.s_nric

INNER JOIN si_results e_2009_fema
    ON e_2009_fema.re_code LIKE 'FEMA%'
       AND e_2009_fema.re_year = '2009'
       AND e_2009_fema.re_semester = '2'
       AND e_2009_fema.re_nric = s.s_nric

LEFT JOIN si_isc_gpa AS isc
    ON isc.isc_g_nric = s.s_nric

WHERE  s.s_admission_year = '2008'
GROUP  BY s.s_nric
ORDER  BY s.s_gender,
          s.s_name ASC

Edit: Included a normalized version:

SELECT s.s_nric                           AS nric,
       s.s_name                           AS name,
       s.s_psle_eng                       AS psle_eng,
       s.s_psle_math                      AS psle_maths,
       s.s_psle_aggr                      AS psle_aggr,
       si_results.re_code                 AS code
       si_results.re_mark                 AS mark
       si_results.re_year                 AS year
       isc.isc_g_gpa                      AS isc_gpa

FROM   si_student_data AS s

INNER JOIN si_results e_2008_feen
    ON si_results.re_nric = s.s_nric

LEFT JOIN si_isc_gpa AS isc
    ON isc.isc_g_nric = s.s_nric

WHERE  s.s_admission_year = '2008'
    AND si_results.re_year in ('2008', '2009')
    AND si_results.re_semester = '2'
    AND (
          si_results.re_code LIKE 'FEEN%'
          OR si_results.re_code LIKE 'FEMA%'
     )

GROUP  BY s.s_nric
ORDER  BY s.s_gender,
          s.s_name ASC

This will produce rows like:

nric: 1
name: 'student 1'
psle_eng: eng1
psle_maths: maths1
psle_aggr: aggr1
code: FEENXXXX
mark: 5
year: 2008
isc_gpa: gpa1



nric: 1
name: 'student 1'
psle_eng: eng1
psle_maths: maths1
psle_aggr: aggr1
code: FEENXXXX
mark: 3
year: 2009
isc_gpa: gpa1


nric: 1
name: 'student 1'
psle_eng: eng1
psle_maths: maths1
psle_aggr: aggr1
code: FEMAXXXX
mark: 4.5
year: 2008
isc_gpa: gpa1


nric: 1
name: 'student 1'
psle_eng: eng1
psle_maths: maths1
psle_aggr: aggr1
code: FEMAXXXX
mark: 5
year: 2009
isc_gpa: gpa1

Notice that the only values changing for the four records is code, mark, and year. You'll get 4 times the number of records back as before but it should run significantly faster than before as well. Your code will have to iterate all the rows and aggregate them as needed.

Answer 3

Try something like this:

  re1.re_mark AS english_2008,
  re2.re_mark AS maths_2008,
  re3.re_mark AS english_2009,
  re4.re_mark AS maths_2009,
  isc.isc_g_gpa AS isc_gpa

FROM   si_student_data AS s
  INNER JOIN si_results as re1 ON re1.re_code LIKE 'FEEN%' AND re1.re_year = '2008' 
            AND re1.re_semester = '2' AND re1.re_nric = s.s_nric
  INNER JOIN si_results as re2 ON re2.re_code LIKE 'FEMA%' AND re2.re_year = '2008' 
            AND re2.re_semester = '2' AND re2.re_nric = s.s_nric
  INNER JOIN si_results as re3 ON re3.re_code LIKE 'FEEN%' AND re3.re_year = '2009' 
            AND re3.re_semester = '2' AND re3.re_nric = s.s_nric
  INNER JOIN si_results as re4 ON re4.re_code LIKE 'FEMA%' AND re4.re_year = '2009' 
            AND re4.re_semester = '2' AND re4.re_nric = s.s_nric    

Replace INNER JOIN with LEFT JOIN if needed

If this solution is still not enough two more options:

• Use Views (1 for each condition you have)

  CREATE VIEW FEEN2008 AS SELECT re_mark, re_nric FROM si_results as re WHERE re.re_code LIKE 'FEEN%' AND re.re_year = '2008' AND re.re_semester = '2'

  and replace original query with something like:

   FROM   si_student_data AS s
      INNER JOIN FEEN2008 as re1 ON re1.re_nric = s.s_nric
      INNER JOIN FEMA2008 as re2 ON re2.re_nric = s.s_nric
      INNER JOIN FEEN2009 as re3 ON re3.re_nric = s.s_nric      
      INNER JOIN FEMA2009 as re4 ON re4.re_nric = s.s_nric
  

  Database engines usually optimize views and it may be faster. Not sure how MySql work with them though.

• Create an index in si_results using fields: re_code, re_year, re_semester and re_nric Beware this will make inserts and updates slower.

Answer 4

sub queries like that always look suspicious to me. It is very tempting to examine other ways to get that data...perhaps left joining on the table? the si_results table is used 3 times and kind of raises a red flag to me as a good candidate to rewrite into a left join. That said though, with only 3000 rows, it really shouldn't be causing much slowness.

Instead, remove the subqueries and check the performance again. Perhaps all you really need is a new index.

Answer 5

NAME IC ENGLISH_2008 ENGLISH_2009 MATHS_2008 MATHS_2009 ISC_GPA
xxx  1    20           40          50            55      2.25

I need like this

Answer 6

 select s.s_nric as NRIC,s.s_name as NAME,s.s_psle_eng as PS_ENG,s.s_psle_math as PS_MATHS ,isc.isc_g_gpa as IS_GPA ,re_feen2007.re_mark AS Eng_2007, re_fema2007.re_mark AS Ma_2007,re_feen2008.re_mark AS Eng_2008, re_fema2008.re_mark AS Ma_2008  from si_student_data as s LEFT JOIN si_isc_gpa as isc ON isc.isc_g_nric=s.s_nric LEFT JOIN si_results as re_feen2007 ON re_feen2007.re_code LIKE 'FEEN0%' AND re_feen2007.re_year = '2007' AND re_feen2007.re_semester = '2' AND re_feen2007.re_nric = s.s_nric LEFT JOIN si_results as re_fema2007 ON re_fema2007.re_code LIKE 'FEMA0%' AND re_fema2007.re_year = '2007' AND re_fema2007.re_semester = '2' AND re_fema2007.re_nric = s.s_nric LEFT JOIN si_results as re_feen2008 ON re_feen2008.re_code LIKE 'FEEN0%' AND re_feen2008.re_year = '2008' AND re_feen2008.re_semester = '2' AND re_feen2008.re_nric = s.s_nric LEFT JOIN si_results as re_fema2008 ON re_fema2008.re_code LIKE 'FEMA0%' AND re_fema2008.re_year = '2008' AND re_fema2008.re_semester = '2' AND re_fema2008.re_nric = s.s_nric where 1=1 AND s.s_admission_year='2007' GROUP BY s.s_nric ORDER BY s.s_gender,s.s_name asc

I created this query followed by your answers. This query gives exact my expecting output. I need values on the single row. But this query also take long time to execute. please advice me.